SELECT COUNT( DISTINCT member_id ) AS active FROM amember_payments WHERE com

Question

So my database has a few hundred thousand members and the processing software was clearly not meant for a database this size. So im having some significant lag issues. I was curious if anyone had a trick for this annoying query.

SELECT COUNT(DISTINCT member_id) AS active
           FROM amember_payments
           WHERE completed > 0 AND expire_date >= '2012-08-01' AND amount > 0

It scans the entire database.

Answer 1

Sounds like you need to add an index. Without knowing your data distribution I cannot tell you which column(s) to add it on for sure but my hunch would suggest 'expire_date' is the best place. Your index should be on the most distinct field to allow sql server to quickly remove rows from consideration without the table scans.

Answer 2

If it scans, you need indexes. From your query, it looks like you should add an index for completed, expire_date, and amount columns. But without knowing the data better, I don't know the exact order I would use.