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.
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]
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.
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
(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)