Slow Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Slow Query

Post 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]
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

What does explain teach you about the query handling? (And tells us something about your indices)
patch2112
Forum Commoner
Posts: 86
Joined: Sun Oct 31, 2004 9:44 am
Location: London

Thanks

Post 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
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
Post Reply