Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
go ahead... be a heretic
 
PerlMonks  

Re: same query, different execution, different performance

by runrig (Abbot)
on Feb 14, 2012 at 02:12 UTC ( #953586=note: print w/ replies, xml ) Need Help??


in reply to same query, different execution, different performance

Update: The OP is cross-posted at StackOverflow (Additional update: And on the DBI-users mailing list)

The db can only use the index if the parameter does not begin with a wildcard. The db can not know at prepare time whether or not your parameter will start with a wildcard. So when it makes the query plan at prepare time, it doesn't know whether or not it can use the index.

This is one of those times when it's best to not prepare the statement with bind parameters. But use $dbh->quote(...) on your parameter and just inline it into the SQL.


Comment on Re: same query, different execution, different performance
Download Code
Re^2: same query, different execution, different performance
by afoken (Parson) on Feb 14, 2012 at 08:43 UTC
    This is one of those times when it's best to not prepare the statement with bind parameters. But use $dbh->quote(...) on your parameter and just inline it into the SQL.

    Don't even think about $dbh->quote(), use SUBSTR instead of LIKE whenever you need to test the start of a string against a LIKE-pattern.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
      Don't even think about $dbh->quote(), use SUBSTR instead of LIKE whenever you need to test the start of a string against a LIKE-pattern

      First, sometimes you should think about using quote(). Second, if you use SUBSTR(), again, the database won't use the index on the column, unless your database supports function based indexes (and I assume Postgres does and that there's an index on Lower(a)), and you have a function based index on the column, etc.

        No, it just depends on the query optimizer. Some databases have query optimizers that know how to use an index when told "LIKE 'blah%'". Some database have query optimizers that know how to use an index when told the equivalent thing using SUBSTR(). Some databases have optimizers that know how to do both. Some neither.

        - tye        

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://953586]
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: (3)
As of 2014-04-21 05:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (490 votes), past polls