Re: LIKE and REGEX optimization

On Jan 15, 2008 2:29 PM, Chris Browne <cbbrowne@acm.org> wrote:
> Kico Zaninetti <kicozaninetti@gmail.com> writes:
> > Hi all.
> >
> > I have a database with 62 million registers and I have to make a
> > SELECT using LIKE.
> >
> > This is my select:
> > SELECT * FROM phone WHERE name LIKE = '%ZANINETTI%' AND city = 'SAO
> > PAULO' AND state = 'SP'

This query is not capable of using an index on name, since you can't
use an index with a like beginning with a %... So

> > I have an index created like this:
> > CREATE INDEX "telefones_idx2" ON "public"."phone"
> >   USING btree ("name" varchar_pattern_ops, "city", "state");

This index serves no purpose, because they get used left to right.
Since you can't match name, the other two parts aren't used.

Does the OP have an index on city,state?  That might help.