very heavy query

[cyclops78]

Hi!

I have to tables with 50.000 rows (lets say 'sales' and 'detail')

The table 'detail' has some rows: 'store', 'departament', 'sub_dep', 'sale_id' that conform a key. The same rows are in the table 'sales'.

Now I have to count all the values in table 'details' where certain restrictions. And for each month of the year display the total count.

here is my query for only one month:

SELECT * FROM sales a, detail b WHERE
(a.store = b.store)
AND (a.departament = b.departament)
AND (a.sub_dep = b.sub_dep)
AND (a.sale_id = b.sale_id)
AND (a.vendor= 'jmss')
AND (YEAR( b.date) = '2003')
AND (MONTH( b.date) = '01')

This query is veeerrrryy slow! ... I can't imagine doing a query for each month in my PHP.

What can I do?... is there a better query?

Thanks In advance!

 

[sleipnir214]

Are all the fields you're using to related the tables indexed?

Is it possible for you to have fewer columns be used to relate the records in the two tables? I would use an auto_increment column in the sales table that would be matched by a sale_ID column in the details table.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[skiflyer]

Quick advice... start with the strongest criteria first... I took a query from 5 seconds to .5 seconds by re-ordering my where clause...

Just guessing at yours I'd bet you can get a significant speed up by moving your MONTH and YEAR clauses to the top...

MySQL will go in order of your clauses, so if limiting it to 2003 will reduce you from 50,000 rows to 10,000... put it first... then use the month to bring it to 5,000... and then vender to make it 2000... now suddenly all that matching is going to happen on alot less rows.

Or perhaps doing vendor first is your fastest... I dunno your data, but you do, so I'd suggest trying something like that.

-Rob

 

[hvass]

I'm with sleipnir214 on this one if you have two tables then design it so you can join on one simple key that you have indexed in advance and get it down to

select
*
from sales a inner join detail b using(compositekey)
where b.date between '2003-01-01' and '2003-01-31'

make sure compositekey and b.date are indexed