Rod, I'm speculating here, but I think what's happening is that too many of
your entries have a PRODUCT_ID that starts with 'DRM' to make your index
useful. When MySQL expects an index query to match more than around 30% of
all rows, it will instead proceed with a table scan, estimating that to be
less expensive overall.
To see more about your indices, run a 'show indexes from MyTable'. The
'cardinality' will be of interest in this situation - when it is low
compared to the number of rows in your table, the index is not as effective
- when it is high, as primary keys are, it is more useful.
If the underscore characters in your query are real underscores and not
wildcards as used in a LIKE query (_ = any character), you can escape them
to speed up your query:
select PRODUCT_ID from My_Table where PRODUCT_ID like 'DRM\_002292\_1055%'
Dan
On 4/5/07, Rod Heyd <[EMAIL PROTECTED]> wrote:
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
