| Bug #3497 | a 'const' type in explain can never get a Using Index | ||
|---|---|---|---|
| Submitted: | 18 Apr 2004 16:27 | Modified: | 4 May 2004 20:20 |
| Reporter: | Harrison Fisk | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.2 | OS: | Linux (Linux) |
| Assigned to: | Michael Widenius | CPU Architecture: | Any |
[4 May 2004 20:20]
Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
This only happens in the case where there was only one row read from the table for the whole SELECT.
Anyway, I have now optimized even this case in MySQL 4.1. Fix will be in 4.1.2
Regards,
Monty
Description: When doing a lookup on a primary key with the CONST explain type, MySQL will always read the data file, never doing an index only query. However for other explain types, it can do it fine. This can actually make regular INDEXs faster than PRIMARY KEYs if you do lookups of this type. How to repeat: -- create and populate the testing table CREATE TABLE bugtest (id int unsigned auto_increment, name char(50), primary key (id)) type=myisam; insert into bugtest (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g'); -- see that the explain doesn't have a Using Index (hence reads the hard drive) explain select 1 from bugtest where id =2; -- execute the query and see in a monitoring program that it really does read the disk, even though it shouldn't have to select 1 from bugtest where id =2; -- drop the primary key, add a regular index ALTER TABLE bugtest DROP PRIMARY KEY, ADD INDEX (id); -- try queries again, notice it doesn't read the data file now, hence making it faster explain select 1 from bugtest where id =2; select 1 from bugtest where id =2; Suggested fix: Fix the optimizer to decide whether to an index-only lookup or whether to read the data file.