From: | Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> |
---|---|
To: | nolan(at)celery(dot)tssi(dot)com, scrappy(at)postgresql(dot)org (The Hermit Hacker) |
Cc: | scrappy(at)postgresql(dot)org (The Hermit Hacker), JanWieck(at)Yahoo(dot)com (Jan Wieck), pgman(at)candle(dot)pha(dot)pa(dot)us (Bruce Momjian), josh(at)agliodbs(dot)com (Josh Berkus), mail(at)joeconway(dot)com (Joe Conway), pgsql-advocacy(at)postgresql(dot)org (\"\"\"Advocacy PostgreSQL\"\"\"), pgsql-general(at)postgresql(dot)org (PostgreSQL-general) |
Subject: | Re: [GENERAL] interesting PHP/MySQL thread |
Date: | 2003-06-23 14:23:23 |
Message-ID: | 5.2.1.1.1.20030623221452.02f7fd48@mbox.jaring.my |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-advocacy pgsql-docs pgsql-general |
At 12:33 AM 6/23/2003 -0500, nolan(at)celery(dot)tssi(dot)com wrote:
> >
> > Oh, you mean like "SELECT * FROM table WHERE field ~* 'nolan';"?
>
>No, I mean as in "SELECT * FROM table WHERE field = 'nolan';"
>
>That will match values with any combination of upper and lower case
>letters that fold to 'nolan': 'Nolan', 'NOLAN', etc.
For me that's a matter of taste. I prefer to use = for case sensitive and
lower(field)=lower('data') for case insensitive. I wonder if there is a
difference between using lower vs upper for case insensitivity but I've
never bothered to look deeply into it.
>Also, unlike PostgreSQL (at least in 7.3), if you define an index on
>the column, mysql appears to use it for LIKE queries.
>
> "SELECT * FROM table WHERE field LIKE 'nolan%';"
>
>is very fast in mysql but not in 7.3, and even non-anchored LIKE searches
>in mysql appear to be using the index.
The versions of Postgresql I've used since I can remember (e.g. at least
v6.5.3 some years ago) use indexes for anchored LIKE searches.
I vaguely recall some people having this "not using index" behaviour when
they are using various locales.
> "SELECT * FROM table WHERE field LIKE '%nolan%';"
>
>executes considerably faster with an index on field than without one.
I think MySQL wins in this one. Just wondering how they do it. And whether
it's a good idea to do it that way.
Regards,
Link.
From | Date | Subject | |
---|---|---|---|
Next Message | Sterling Hughes | 2003-06-23 15:06:51 | Re: [GENERAL] interesting PHP/MySQL thread |
Previous Message | scott.marlowe | 2003-06-23 13:22:40 | Re: [GENERAL] interesting PHP/MySQL thread |
From | Date | Subject | |
---|---|---|---|
Next Message | Reuben D. Budiardja | 2003-06-23 15:43:29 | Re: [GENERAL] interesting PHP/MySQL thread |
Previous Message | Jan Wieck | 2003-06-23 12:21:44 | Re: [GENERAL] interesting PHP/MySQL thread |
From | Date | Subject | |
---|---|---|---|
Next Message | Johnson, Shaunn | 2003-06-23 14:24:14 | missing chunk number error? |
Previous Message | Kallol Nandi | 2003-06-23 13:24:24 | Lotus Domino and PostgreSql in Linux |