[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