Optimizing MySQL Queries

[Noah]

Hello,

I've been told I need to optimize my queries in order to reduce server load, so I'm trying to figure out the best way to do that. Here's an example...

My original query looks like this:

SELECT DISTINCT artist.name,release.name,format.name,format.price,orderitem.quant ity,item.itemID FROM item LEFT JOIN release ON item.releaseID=release.releaseID,format,artist,orderitem WHERE item.formatID=format.formatID AND artist.artistID=release.artistID AND orderitem.itemID=item.itemID AND orderitem.orderID=46

When I use EXPLAIN with it, I get these results:

table[nbsp][nbsp][nbsp][nbsp]|type[nbsp][nbsp]|possible_keys|key[nbsp][nbsp][nbsp][nbsp]|key_len|ref[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |rows|Extra
---------|------|-------------|-------|-------|----------------|----|----------
item[nbsp][nbsp][nbsp][nbsp] |ALL[nbsp][nbsp] |PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|25[nbsp][nbsp]|
release[nbsp][nbsp]|eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|item.releaseID[nbsp][nbsp]|1[nbsp][nbsp] |
format[nbsp][nbsp] |eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|item.formatID[nbsp][nbsp] |1[nbsp][nbsp] |
artist[nbsp][nbsp] |eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|release.artistID|1[nbsp][nbsp] |
orderitem|ALL[nbsp][nbsp] |[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|108 |where used
-------------------------------------------------------------------------------

I added an index to orderitem.orderID and changed the query to:

SELECT artist.name,release.name,format.name,format.price,orderitem.quant ity,item.itemID FROM orderitem STRAIGHT_JOIN item LEFT JOIN release ON item.releaseID=release.releaseID,format,artist WHERE item.formatID=format.formatID AND artist.artistID=release.artistID AND orderitem.itemID=item.itemID AND orderitem.orderID=46

The EXPLAIN output now looks like this:

table[nbsp][nbsp][nbsp][nbsp]|type[nbsp][nbsp]|possible_keys|key[nbsp][nbsp][nbsp][nbsp]|key_len|ref[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |rows|Extra
---------|------|-------------|-------|-------|----------------|----|-----
orderitem|ref[nbsp][nbsp] |orderID[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|orderID|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|???[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp][nbsp] |5[nbsp][nbsp] |
item[nbsp][nbsp][nbsp][nbsp] |eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|orderitem.itemID|1[nbsp][nbsp] |
release[nbsp][nbsp]|eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|item.releaseID[nbsp][nbsp]|1[nbsp][nbsp] |
format[nbsp][nbsp] |eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|item.formatID[nbsp][nbsp] |1[nbsp][nbsp] |
artist[nbsp][nbsp] |eq_ref|PRIMARY[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|PRIMARY|4[nbsp][nbsp][nbsp][nbsp][nbsp][nbsp]|release.artistID|1[nbsp][nbsp] |
--------------------------------------------------------------------------

Is this the type of result I'm looking for? I see that the number of rows has been reduced. Is there something else I should be looking for?

Thanks,
--Noah
[This message has been edited by Noah (edited 11-17-00@7:22 pm)]