MySQL Forums
Forum List  »  Optimizer & Parser

B-Tree index ignored. Why?
Posted by: Daniel Terry
Date: March 14, 2007 12:46PM

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 | 
+----+-------------+--------------------------+------+---------------+------+---------+------+-------+-------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
B-Tree index ignored. Why?
7013
March 14, 2007 12:46PM
2666
March 14, 2007 07:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.