Optimize Query for MySQL

Question

I want to run a query that generates a revenue report for campaigns. There are 2 tables members and payments.

members (id, campaign_code)
payments (id, member_id, amount)

I want to create a table that groups by campaign_code, ie in this format

campaign_code, member_count, total_revenue

I am putting all campaigns into an array and running this,

SELECT sum( amount ) AS amt
FROM (members
INNER JOIN payments 
    ON payments.member_id = members.id
)
WHERE campaign_code = 'XX'

and it is taking a LOT of time. Anyway to optimize this or do it in a single query?

Yes both have indexes. There would be about 75K members and 80K rows in payments. Not a lot. — daydreamer
– daydreamer, Commented Apr 16, 2012 at 16:33
'...where campaign_code in (xx,yy,zz)'. Do the loop to build up a single sql statement that looks like that. — Stevo
– Stevo, Commented Apr 16, 2012 at 16:35

Marcus Adams · Accepted Answer · 2012-04-16 19:05:45Z

1

As you said that you need aggregation for all campaign code, try this

SELECT m.campaign_code , count(p.member_id) AS member_count,
  SUM( amount ) AS total_revenue
FROM members m, payments p
WHERE p.member_id = m.id
GROUP BY campaign_code;

Make sure to read on mysql group by function

edited Apr 16, 2012 at 19:05

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

answered Apr 16, 2012 at 17:32

Bharath

1665 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DRapp · Accepted Answer · 2012-04-16 17:05:03Z

0

payments (id, member_id, amount) I want to create a table that groups by campaign_code, ie in this format

campaign_code, member_count, total_revenue I am putting all campaigns into an array and running this,

select
      m.Campaign_Code,
      count( distinct p.member_id) as Members,
      count(*) as PaymentEntries,
      sum( p.amount ) as TotalRevenue
   from
      members m
         join Payments p
            on m.id = p.member_id
   where
      m.campaign_code = 'XX'

If you want all campaigns, just remove the WHERE clause. You mentioned in comments that the tables DO have indexes, but I would ensure that members table has index on campaign_code, and payments has index on member_id.

This query will give a count of distinct members who've contributed, total number of contributions (in case one or more members contributed multiple times), and the totals of all the contributions.

answered Apr 16, 2012 at 17:05

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

1 Comment

daydreamer Over a year ago

Thanks Drapp, but this solution gives me an overall picture. I actually wanted data based on campaign codes, i figured it out.

Saic Siquot · Accepted Answer · 2012-04-16 16:36:17Z

0

use

where campaing_code in ('XX','YY','ZZ','AA','BB')

and have an index on campaing_code

answered Apr 16, 2012 at 16:36

Saic Siquot

6,5135 gold badges37 silver badges56 bronze badges

Collectives™ on Stack Overflow

Optimize Query for MySQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related