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.