Hi Folks,
I'm getting some unexpectedly poor performance from some queries using the
like operator.
The table is an Innodb table with an index on the column appearing in a like
conditional.
Explain gives me this:
[localhost]>explain select PRODUCT_ID from My_Table where PRODUCT_ID like
'DRM_002292_1055%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: My_Table
type: ALL
possible_keys: PRODUCT_ID
key: NULL
key_len: NULL
ref: NULL
rows: 87684
Extra: Using where
1 row in set (0.00 sec)
[localhost]>
Now, this does not make any sense to me at all. As you can see, the
PRODUCT_ID column is already indexed, and in fact, the pattern in the like
operator argument should reduce the number of rows to scan down to about 50,
and yet, mysql wants to examine practically the entire table. Adding a
force index(PRODUCT_ID) seems to help a little bit, but still not like it
should. Any ideas what could be happening? I'm seeing the same behavior on
both mysql 5.0.37 and an older system running mysql 4.0.
Thanks.
Rod Heyd
