Re: Perl Binding affects speed?

On Fri, Feb 25, 2011 at 05:02, Sam Wong <sam(at)hellosam(dot)net> wrote:
> * But if I do this - using binding:
> $dbh->selectall_arrayref("SELECT * from shipment_lookup WHERE (UPPER(lookup)
> LIKE ?)", undef, '0GURG5YGVQA9%');
> It took 10 seconds to finish the query, just like it was using full table
> scan instead! Even though the 'explain' shows the same query plan.

This is a pretty common shortcoming with placeholders. Since planning
of parameterized queries is done *before* binding parameters, the
planner has no knowledge of what the "?" placeholder actually is. Thus
it often gets the selectivity statistics wrong and produces worse
plans for your values.

AFAIK the only workaround is to not use variable binding in these
cases, but escape and insert your variables straight it into the SQL
query.

Regards,
Marti