List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 29 1999 5:55pm
Subject:Are char columns indexed on 3 chars minimum ?
View as plain text  
>>>>> "Patrice" == Patrice Bulat <bulat@stripped> writes:

Patrice> Hello,
Patrice> I have a table containing approx. 87000 records, with a indexed varchar(5)
Patrice> column.
Patrice> A prefix search using the 3 first characters will use the index.
Patrice> A prefix search using the 2 first characters won't use the index.
Patrice> Is that a limitation on varchar keys ? I guess it has something to do with
Patrice> the B-tree implementation, but I haven't found anything discussing this in
Patrice> the online manual.
Patrice> Is there a way to make the second search use a index (without duplicating
Patrice> too much) ?

mysql> describe my_table;

Patrice> Field	Type	Null	Key	Default	Extra	
Patrice> -----	----	----	---	-------	-----	
Patrice> [...]
Patrice> cp	varchar(5)		MUL			

>> explain select * from my_table where cp like '347%';

Patrice> table	type	possible_keys	key	key_len	ref	rows	Extra	
Patrice> -----	----	-------------	---	-------	---	----	-----	
Patrice> my_table	range	cp	NULL	NULL	NULL	1157	range used on key cp	

>> explain select * from my_table where cp like '34%';

Patrice> table	type	possible_keys	key	key_len	ref	rows	Extra	
Patrice> -----	----	-------------	---	-------	---	----	-----	
Patrice> my_table	ALL	cp	NULL	NULL	NULL	87101	where used	

Hi!

MySQL will not use indexes if the distribution of keys suggest that
it's faster to use table scanning than use keys.

(The MySQL email archive have MANY mails about this topic)

Yours,
Monty

*************** Warning commercial signature follows **********
If you like TCX's concept of a 'mostly free' database and free
advice, you should at least CONSIDER supporting us that we can
afford to keep this service up.   http://www.mysql.com/
Thread
Are char columns indexed on 3 chars minimum ?Patrice Bulat27 Aug
  • Are char columns indexed on 3 chars minimum ?Michael Widenius29 Aug