List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 27 2000 10:56pm
Subject:Re: query optimization question
View as plain text  
In the last episode (Nov 27), Mike Wexler said:
> I have the following query:
> 
> SELECT item.itemKey, item.storeId, LEFT(item.title, 40), item.price,
>        thumbs.width, thumbs.height, thumbs.relativeUrl
> FROM inventory.thumbs
> STRAIGHT_JOIN inventory.item
> STRAIGHT_JOIN account.account
> STRAIGHT_JOIN inventory.status_defs
> WHERE account.accountKey = thumbs.accountKey
> AND   account.active = 1
> AND   thumbs.groupKey = 1
> AND   item.itemKey = thumbs.itemKey
> AND   inventory.status_defs.statusId = item.status
> AND   inventory.status_defs.showItems = 1
> ORDER BY inventory.thumbs.enteredStamp DESC
> LIMIT 0, 24
> 
> explain tells me:
> 
>
> +-------------+--------+-----------------------------+----------+---------+-------------------+--------+----------------------------+
> | table       | type   | possible_keys               | key      | key_len | ref      
>         | rows   | Extra                      |
>
> +-------------+--------+-----------------------------+----------+---------+-------------------+--------+----------------------------+
> | thumbs      | ref    | PRIMARY,groupKey,accountKey | groupKey |       4 | const    
>         | 212940 | where used; Using filesort |
> | item        | eq_ref | PRIMARY                     | PRIMARY  |       4 |
> thumbs.itemKey    |      1 | where used                 |
> | account     | eq_ref | active,PRIMARY              | PRIMARY  |       4 |
> thumbs.accountKey |      1 | where used                 |
> | status_defs | eq_ref | PRIMARY                     | PRIMARY  |      64 |
> item.status       |      1 | where used                 |
>
> +-------------+--------+-----------------------------+----------+---------+-------------------+--------+----------------------------+
> 4 rows in set (3.84 sec)

When you paste tables this wide, make sure you turn off word wrap; your
email was unreadable as sent.

You can try using the USE INDEX or IGNORE INDEX clauses to the SELECT
statement to force mysql to use the indexes you want.

> I've included below information on all the tables involved.

It won't make any difference, but your itemKey fields don't match:

> mysql> show fields from inventory.thumbs;
> | itemKey        | int(8)          
>
> mysql> show fields from inventory.item;
> | itemKey        | int(8) unsigned 

-- 
	Dan Nelson
	dnelson@stripped
Thread
query optimization questionMike Wexler27 Nov
  • Re: query optimization questionDan Nelson27 Nov