Beefy Boxes and Bandwidth Generously Provided by pair Networks vroom
P is for Practical
 
PerlMonks  

Re: OT: Sql statement (how to ignore lower-upper case)

by pelagic (Curate)
on Dec 15, 2004 at 11:56 UTC ( #414995=note: print w/ replies, xml ) Need Help??


in reply to OT: Sql statemet (how to ignore lower-upper case)

And this still has absolutely nothing to do with Perl but I just have to say it again:
Watch out using functions like lower, upper or what not in WHERE clauses like:

select name from table where lower(name) = 'fubar'
Using a function in a where clause on a database column prevents the database from using any indexes. It has to apply the function to all rows first before it can decide about the selection and that's disastrous for performance if it is a big table.

pelagic


Comment on Re: OT: Sql statement (how to ignore lower-upper case)
Download Code
Re^2: OT: Sql statement (how to ignore lower-upper case)
by thor (Priest) on Dec 15, 2004 at 13:46 UTC
    I realize that your statement is true for most database engines, but Postgres has a nifty feature.

    thor

    Feel the white light, the light within
    Be your own disciple, fan the sparks of will
    For all of us waiting, your kingdom will come

      Interesting!
      Even Oracle got their Funcion Based Index. If you know in advance that you will be using something like that, it helps a lot!

      pelagic
        From reading the doc, it is of significant note that it's expensive to maintain that index, as it needs to be re-computed for every insert and update. It's not something to take lightly. However, it might make sense to do this on a relatively static database, or one in which the maintainence time is irrelevant, but access time is key.

        thor

        Feel the white light, the light within
        Be your own disciple, fan the sparks of will
        For all of us waiting, your kingdom will come

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://414995]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (6)
As of 2014-04-18 05:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (461 votes), past polls