509787 need help speeding up a mysql query

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 :)

Beceriler: Her şey Kabul, MySQL, PHP

Daha fazlasını görün: n query, invoice works, mysql query, invoices/month, data query, php database invoice number, query query, mysql query php, php mysql number invoice, need faster, help current, count mysql, need ids, php run faster, php mysql count, takes data mysql, query database, php query result, php mysql invoice database, php mysql invoice, php mysql array, mysql records, mysql previous day, mysql invoices, mysql invoice database

İşveren Hakkında:
( 104 değerlendirme ) Mebane, United States

Proje NO: #2255716



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.

0 gün içinde 75$ USD
(0 Değerlendirme)