pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: nolan@celery.tssi.com
To: scrappy@postgresql.org
Cc: scrappy@postgresql.org
Cc: JanWieck@Yahoo.com
Cc: pgman@candle.pha.pa.us
Cc: josh@agliodbs.com
Cc: mail@joeconway.com
Cc: pgsql-advocacy@postgresql.org
Subject: Re: [GENERAL] interesting PHP/MySQL thread
Date: Mon, 23 Jun 2003 00:33:51 -0500 (CDT)
Message-ID: <20030623053352.1457.qmail@celery.tssi.com> (raw)
In-Reply-To: <20030623015333.E95856@hub.org>

> > Personally, if I want case insensitivity, I'll WRITE IT INTO THE CODE,
> > but I can see how some people might think that 'NOLAN', 'Nolan' and
> > 'nolan' should be considered as the same data.
> 
> 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. 

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.
  
   "SELECT * FROM table WHERE field LIKE '%nolan%';" 

executes considerably faster with an index on field than without one.
--
Mike Nolan



view thread (72+ messages)  latest in thread

Message-ID: <20030623053352.1457.qmail@celery.tssi.com>
Permalink:  ../20030623053352.1457.qmail@celery.tssi.com/
Also on:    postgresql.org/message-id/20030623053352.1457.qmail@celery.tssi.com

 · 

reply
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-docs@postgresql.org
  Cc: nolan@celery.tssi.com, scrappy@postgresql.org, JanWieck@Yahoo.com, pgman@candle.pha.pa.us, josh@agliodbs.com, mail@joeconway.com, pgsql-advocacy@postgresql.org
  Subject: Re: [GENERAL] interesting PHP/MySQL thread
  In-Reply-To: <20030623053352.1457.qmail@celery.tssi.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