Ben Edwards wrote:
On 21/06/07, Baron Schwartz <[EMAIL PROTECTED]> wrote:
Hi Ben,
Ben Edwards wrote:
> Can Mysql 4.20 use an index where like 'xxxxxx%' is in the where
> clause against the column.
>
> i.e. index table1(col1)
>
> from table1
> where table1.col1 like 'something%'
Yes, it can use an index on LIKE prefixes. Using a function such as
LEFT(col1, 9) =
'something' will not work though.
You can always tell whether the index is used by examining EXPLAIN.
We did use EXPLAIN and it said it was not using the index. The index
we wanted to use was a primary key.
The index may not be selective enough and is being ignored, or an ORDER BY or similar
might be making the optimizer think it's too expensive to use. Is the index in
possible_keys? If not, you may have a collation or charset problem. Give the output
of SHOW CREATE TABLE if you think it will help.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
