Page 1 of 1

Slow Query

Posted: Wed Aug 01, 2007 4:35 pm
by patch2112
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello all,

I have a MySQL query that is taking 30 seconds to run.  

[syntax="sql"]SELECT orders.order_id, item_codes.stock_code, products.name, order_items.quantity, order_items.final_unit_price
FROM orders, products, order_items, item_codes
WHERE orders.order_id = order_items.order_id
and products.product_id = order_items.item
and order_items.item = item_codes.product_id
and order_items.color = item_codes.color_id
and order_items.size_id = item_codes.size_id
and orders.order_id IN(2742,2743)
order by order_id desc
None of these tables are huge and none of them have tons of data either. It had a sub-query before within the IN statement, but it's still slow once I put in hard-coded values like above.

Does anyone know of a way to speed this up?

Many thanks!
Philip


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Aug 01, 2007 4:51 pm
by timvw
What does explain teach you about the query handling? (And tells us something about your indices)

Thanks

Posted: Wed Aug 01, 2007 5:11 pm
by patch2112
Thanks for pointing me in the right direction timvw.

I didn't really understand indexes before and had never came across a problem until now. I added an index to the order_items.order_id (not a primary key in this table) and it dropped the execution time to 0.05 sec. Marvelous.

Thanks a bunch!
Philip

Posted: Wed Aug 01, 2007 5:27 pm
by nathanr
now thats a perfect set of posts.. somebody who can simply be pointed in the right direction and work the rest out for themselves, most impressed :D

(look ip JOINS when you get a chance, the structure of the SQL query should let you drop that time down a little further, but ya indexes where the way to go)

Posted: Thu Aug 02, 2007 1:51 am
by timvw
Btw, i've got the feeling that my first suggestion was misunderstood... Here's what i meant with http://dev.mysql.com/doc/refman/5.0/en/explain.html...