[thelist] mysql index not being used in query

Keith Gaughan keith at digital-crew.com
Mon May 9 15:43:30 CDT 2005


Sarah Sweeney wrote:

> I have a table called "Cats" with the following fields (other fields
> omitted to simplify):
> 
> CategoryID
> NodeLeft
> NodeRight
> Title
> 
> I've added indexes on
> 
> CategoryID -> CatID
> NodeLeft, NodeRight -> NodeLR
> 
> I ran this EXPLAIN statement:
> 
> EXPLAIN SELECT CategoryID, Title, NodeLeft, NodeRight
>           FROM Categories
>          WHERE NodeLeft BETWEEN 2 AND 1705
>       ORDER BY NodeLeft
> 
> which returned the following:
> 
> table | type | possible_keys | key    | key_len | ref    | rows | Extra
> ------+------+---------------+--------+---------+--------+------+----------------------------
> 
> Cats  | ALL  | NodeLR        | (NULL) | (NULL)  | (NULL) | 852  | Using
> where; Using filesort
> 
> I'm not really sure why the query isn't using the NodeLR index - any ideas?


First thing to know is that MySQL only ever uses one (yes, one) index.
Now, NodeLR is a compound index, meaning that it will only be used if
*both* columns are involved in the lookup. What you probably need is
a seperate index on NodeLeft so that it will do the lookup on that.

K.


More information about the thelist mailing list