B-Tree index ignored. Why?

[Daniel Terry]

A B-Tree index is being ignored for no reason I can think of, causing queries to scan the entire table (30x slower). Strangely, it works fine if I just select just the primary key, but ignores the index if I select any other fields. Forcing the index does nothing. As a temporary fix, I am just retrieving the primary keys and doing a second query for each row to get the rest of the data, which is very fast. What am I doing wrong?


(tStart,tEnd) is indexed as a B-Tree. This is the command I used to create it:

CREATE INDEX span_index using BTREE ON pslx (tStart,tEnd);


Below are the table definition and the queries I am executing.

mysql> desc pslx;
+-----------------+------------------+------+-----+---------+
| Field           | Type             | Null | Key | Default |
+-----------------+------------------+------+-----+---------+
| qName           | varchar(255)     | NO   | PRI |         |       
| tName           | varchar(255)     | YES  |     | NULL    |  
| tStart          | int(10) unsigned | YES  | MUL | NULL    |       
| tEnd            | int(10) unsigned | YES  | MUL | NULL    |  
+-----------------+------------------+------+-----+---------+

mysql> EXPLAIN SELECT qName FROM pslx WHERE 153742088 BETWEEN tStart and tEnd;
+----+-------------+--------------------------+-------+---------------+------------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows  | Extra                    |
+----+-------------+--------------------------+-------+---------------+------------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | pslx  | index | NULL          | span_index | 10      | NULL | 25298 | Using where; Using index | 
+----+-------------+--------------------------+-------+---------------+------------+---------+------+-------+--------------------------+

mysql> EXPLAIN SELECT qName,tName FROM pslx WHERE 153742088 BETWEEN tStart and tEnd;
+----+-------------+--------------------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+--------------------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | pslx  | ALL  | NULL          | NULL | NULL    | NULL | 25298 | Using where | 
+----+-------------+--------------------------+------+---------------+------+---------+------+-------+-------------+