>>>>> "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/
