Re: "like" and index

Andrzej Zawadzki wrote:
> Daniel J. Summers wrote:
>
>> Tony Liao wrote:
>>
>>> I try to explain analyze,but it doesn't work ,it use seq scan.
>>>
>> Generally speaking, LIKE doesn't use indexes.
>>
> ?! That's not true at all!!
>
MySQL will only use it if the wildcard isn't in the front (1) and
requires the MATCH keyword to search full-text indexes (2), Oracle
requires special "full-text" indexes to be able to use for LIKE (3)
(actually dealt with that at work a few months back), SQL Server only
uses it under certain conditions (4), and even PostgreSQL (the great
subject of this mailing list) doesn't do it with a standard index (5) -
you've got to use a special operator class. I know that Unisys RDMS
doesn't look at indexes for a LIKE clause either, but most folks here
will probably never use that.

In my experience, the only times LIKE should be used is when the table
being searched is small, performance doesn't matter, or there's not
really any other way to get at the data. And, for the latter, there is
usually some other way to get data if one thinks outside the box a bit;
and, when there's not another way, the full-text or patterned indexes
are the way to go. Performance-wise, it's a pitfall that you've got to
ensure you know how to use.

1 - http://www.webmasterworld.com/forum88/9286.htm
2 - http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
3 - http://www.dba-oracle.com/oracle_tips_like_sql_index.htm
4 -
http://www.sql-server-performance.com/articles/dev/sql_best_practices_p1.aspx
5 - http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html

--
Daniel J. Summers
*Owner, DJS Consulting* Support <http://support.djs-consulting.com/>
• Tech Blog <http://www.djs-consulting.com/linux/blog>

daniel(at)djs-consulting(dot)com <mailto:daniel(at)djs-consulting(dot)com> •
http://www.djs-consulting.com <http://www.djs-consulting.com/>

GEEKCODE 3.12 GCS/IT d s-:+ a C++ L++ E--- W++ N++ o? K- w !O M--
V PS+ PE++ Y? !PGP t+ 5? X+ R* tv b+ DI++ D+ G- e h---- r+++ y++++