Very long query doubt... (Monster query...)
Posted by: Toni Lopez
Date: March 24, 2009 12:53PM
Hi all, Im a newby, but I have got a very big problem with time and big big querys...
I have a table with 6Million, more or less, records, and I have to do a few querys on it, always selects, but, the length of the querys are... THE BIGGEST ONES!!
Try to imagine a table with 8 columns: data,name,shop_id,article_name,class_code, sells, quantity, discount
I have to search wich information(sum's of sells,quantities and discounts) is, between two dates and a very long list of pairs mades of article_name and class_code, so we will have:
select sum(sells),sum(quantity),sum(dto * quantity) from table where data >= "2008-01-01" and data <= "2009-01-01" and ( ( article_name="A1" and class="N" ) or ( article_name="A2" and class="N" ) or ( article_name="A3" and class="N" ) ... )
In true querys I have almost 200 or more pairs of article_name and class for each query. Class not always have the same value, and of course article_name is always different.
Making an Index on data I have the best resoults, talking about 3 to 4 million rows. Thats very very slow, and I have to do the same querys (but not always), in different interval values in data.
I tried putting an Index in article_name, and in the pair article_name <-> class, and tried other ones combining data with these 2, but, the best resoults were always using the data index alone.
So, can anyone help me out how to make down the execution time on this monster query???
Thanks to all.
Edited 1 time(s). Last edit at 03/24/2009 01:00PM by Toni Lopez.