Re: same query, different execution, different performance

[runrig]

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.

[afoken]

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

[runrig]

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.

[tye]

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.