[thelist] mysql index not being used in query

[Matt Warden]

On 5/9/05, Sarah Sweeney <mr.sanders at designshift.com> 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?

I believe it is because you are not using NodeRight in an AND
condition in the WHERE clause. You can confirm this by adding "FORCE
INDEX" to the SQL query. If the EXPLAIN still doesn't use the index,
then using the index is not possible (likely due to what I suggest
above).

If this is the case, create separate indexes for NodeLeft and
NodeRight, if you plan on using queries that reference only one of
them in an AND-type condition.

Here's the specific query you want to run to confirm this:

EXPLAIN SELECT CategoryID, Title, NodeLeft, NodeRight
       FROM Categories FORCE INDEX NodeLR
       WHERE NodeLeft BETWEEN 2 AND 1705
       ORDER BY NodeLeft



-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.