MySQL Forums :: Newbie :: Very long query doubt... (Monster query...)


Advanced Search

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.

Options: ReplyQuote


Subject Written By Posted
Very long query doubt... (Monster query...) Toni Lopez 03/24/2009 12:53PM
Re: Very long query doubt... (Monster query...) Rick James 03/25/2009 06:46PM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/30/2009 08:55AM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/30/2009 09:00AM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/30/2009 09:02AM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/30/2009 09:04AM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/30/2009 09:05AM
Re: Very long query doubt... (Monster query...) Peter Brawley 03/30/2009 09:15AM
Re: Very long query doubt... (Monster query...) Rick James 03/30/2009 08:04PM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/31/2009 01:00AM
Re: Very long query doubt... (Monster query...) Rick James 03/31/2009 08:30AM
Re: Very long query doubt... (Monster query...) Toni Lopez 03/31/2009 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.