From: Daniel J. Summers <daniel@djs-consulting.com>
To: pgsql-admin@postgresql.org
Subject: Re: "like" and index
Date: Wed, 25 Feb 2009 17:03:31 -0700
Message-ID: <49A5DC53.7030107@djs-consulting.com> (raw)
In-Reply-To: <49A5B703.7080700@gmail.com>
References: <8f750b7c0902250259w6065515as350aca3b5d7d8173@mail.gmail.com>
<49A553B8.2040009@djs-consulting.com>
<49A5B703.7080700@gmail.com>
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@djs-consulting.com <mailto:daniel@djs-consulting.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++++
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: daniel@djs-consulting.com
Subject: Re: "like" and index
In-Reply-To: <49A5DC53.7030107@djs-consulting.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox