[thelist] mysql index not being used in query

[Sarah Sweeney]

>>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).

As per your suggestion, I tried:

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

and got :

table | type  | possible_keys | key    | key_len | ref    | rows | Extra
------+-------+---------------+--------+---------+--------+------+------------
Cats  | range | NodeLR        | NodeLR | 4       | (NULL) | 838  | Using 
where

So obviously the index can be used without including both columns in the 
where clause. Also, the MySQL docs say:

"If the table has a multiple-column index, any leftmost prefix of the 
index can be used by the optimizer to find rows. For example, if you 
have a three-column index on (col1, col2, col3), you have indexed search 
capabilities on (col1), (col1, col2), and (col1, col2, col3)."

Looks like maybe my only option is to include "FORCE INDEX (NodeLR)" in 
this query. <sigh>