very heavy query !

cyclops78

Hi!

I have to tables with 50.000 rows each (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 way?

Thanks In advance!

LordShryku

WHERE a.store = b.store
AND a.departament = b.departament
AND a.sub_dep = b.sub_dep
AND a.sale_id = b.sale_id

All this seems kinda uneccesary. If one match can do it, narrow it down. Now, to do the whole year, I'd do something like this

SELECT   a.store, 
         month(b.date), 
         count(b.*)
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'
GROUP BY month(b.date)

nemonoman

Are any of these columns indexed?

Since your tables have a compound key, it would be reasonable to index

'store', 'departament', 'sub_dep', 'sale_id'

on both tables.

I suspect, however that you could make 'sale_id' a unique key that would be present in both tables.

Then your query could be:

WHERE a.sale_id = b.sale_id
AND a.vendor= 'jmss'
AND year(b.date) = '2003'
GROUP BY month(b.date)

Assuming sale_id is a unique key, if you indexed sales.vendor and 'sale_id' in both tables, your query would be fast indeed.