MySQL Bugs: #5138: INDEX CHOISE ON OPTIMIZER

Bug #5138	INDEX CHOISE ON OPTIMIZER
Submitted:	21 Aug 2004 21:46	Modified:	16 Nov 2004 22:36
Reporter:	Roberto Spadim (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3 maybe others	OS:	Linux (Linux maybe others)
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
optimizer choise a BTREE index when searching using =
like:

select * from table where a='value';

i have two index one btree(a) and other hash(a)

but seeking on a it's always use btree index... :(

mysqld don't have a index type choise when optimizing query?

How to repeat:
create a table with btree index and a hash index on the same field
after search on that field
like
select * from table where field='value'

the explain show the indexes but prefer the btree index

I verified this on linux (2.4.21 #12 SMP) and windows 2000 SMP.  A heap index should almost 
always be used when searching for a constant string as I am in this example unless maybe we're 
also ordering by the column and there are many matches.

drop table if exists heaptest;

create table heaptest (id int unsigned not null primary key auto_increment, name varchar(20) not 
null ) type=heap;

insert into heaptest (name) values ('Matt'), ('Lilu'), ('Corbin'), ('Carly'), ('Suzy'), ('Hoppy'), 
('Burrito'), ('Mimi'), ('Sherry'), ('Ben'), ('Phil'), ('Emily'), ('Mike');

alter table heaptest add index heap_idx (name);

alter table heaptest add index btree_idx using btree (name);

explain select * from heaptest where name='matt'\G

ChangeSet@1.1994, 2004-09-08 02:07:53+04:00, sergefp@mysql.com

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