Do you train Kung Fu?
Or know someone who does?
Then check out KungFuPeople.com
Mobile version of this pageSqueezebox + Pandora
Next:
Carbon XEmacs installed
Related blogs
PostgreSQL, MySQL or SQLiteAnti-spamming email harvesting
Optimized stylesheets
Creating a user for postgresql
Adding a year in PostgreSQL
Regular Expressions in Javascript cheat sheet
\b in Python regular expressions
\B in Python regular expressions
Python regular expression tester
Are you a web developer? Then VisiBone is for you
UPPER vs. ILIKE
Date formatting in python or in PostgreSQL
Integer division in programming languages
Running simple SQL commands on the command line
pg_class to check if table exists
List of casts in PostgreSQL
Just Oracle and IBM?
ALTER TABLE patch
Date formatting in Python or in PostgreSQL (part II)
Sorting transform function in PostgreSQL
Why bother with MySQL...
To sub-select or not sub-select in PostgreSQL
Speed test between django_mongokit and postgresql_psycopg2
Fastest "boolean SQL queries" possible with Django
Optimization of getting random rows out of a PostgreSQL in Django
Connecting with psycopg2 without a username and password
The problem with CSS
Python optimization anecdote
Fastest way to uniqify a list in Python
More optimization of Peterbe.com - CSS sprites
DoneCal homepage now able to do 10,000 requests/second
Optimization story involving something silly I call "dict+"
Related by category
Work
Quick PostgreSQL optimization story
11th of March 2006
There are several ways to do case insensitive string matching in SQL. Here are two ways that I've tried and analyzed on a table that doesn't have any indices.
Option 1:
LOWER(u.first_name) = LOWER('Lazy') OR
LOWER(u.last_name) = LOWER('Lazy') OR
LOWER(u.first_name || u.last_name) = LOWER('Lazy')
)
Option 2:
u.first_name ILIKE 'Lazy' OR
u.last_name ILIKE 'Lazy' OR
u.first_name || u.last_name ILIKE 'Lazy'
)
A potentially third option is to make sure that the parameters sent to the SQL code is cooked, in this case we make the parameter into lower case before sent to the SQL code
Option 1b:
LOWER(u.first_name) = 'lazy' OR
LOWER(u.last_name) = 'lazy' OR
LOWER(u.first_name || u.last_name) = 'lazy'
)
Which one do you think is fastest?
The results are:
Option 1b: 2.0ms - 2.1ms (average 2.05ms)
Option 2: 1.7ms - 2.0ms (average 1.85ms)
Conclusion: the ILIKE operator method is the fastest. Not only is it faster, it also supports regular expressions.
I've always thought that the LIKE and ILIKE were sinfully slow (yet useful when time isn't an issue). I should perhaps redo these tests with an index on the first_name and last_name columns.
Try
CREATE INDEX u_first_name_index ON u (lower(first_name));
or a variation thereof.
(Ah, I just noticed you wrote "without any indices". You probably already know this, then. I'll post it anyway - for the search engines.)
You could also use a shadow column maintained by a trigger, but that's an evil solution, only to be used in almost never-met circumstances.