CODEWITHSUNDEEP
mysqloptimization
daydreamer
daydreamer

Reputation: 153

Optimize Query for MySQL

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?

Upvotes: 0

Views: 83

Answers (3)

Bharath
Bharath

Reputation: 166

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

Upvotes: 1

DRapp
DRapp

Reputation: 48129

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.

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

use 

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

and have an index on campaing_code

Upvotes: 0

Related Questions