The end result of the query is to find the total number of invoices for previous months in a database for anyone who has an invoice in the current month.
Database is 600K records
Currently my query goes like this:
Find the Contact_id for everyone who has an invoice in the current month.
Make an array out of that list. Typical result is doing to be around 5K Ids.
Then I do a 'for each' in php that then looks up the total count of invoices for each of those Ids where the data is before the end date of the query (i.e. last day of the current month)
I then total up all of the invoices and divide by the number of contact_ids.
My current setup works, BUT it takes probably 20 minutes to run, which is too long for my taste. I'm sure there's a better/faster way. Help me find it :)
Bid is for writeup regarding the optimization of the table referenced in the project message board in relation to the query being executed. This is upon request by client via email.