Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re^2: DBI placeholders and like statement

by jfroebe (Vicar)
on Jun 18, 2013 at 17:08 UTC ( #1039606=note: print w/ replies, xml ) Need Help??


in reply to Re: DBI placeholders and like statement
in thread DBI placeholders and like statement

Don't I have egg on my face! :) I was putting the percent sign in the query itself.

Thanks again!

Jason L. Froebe

Blog, Tech Blog


Comment on Re^2: DBI placeholders and like statement
Re^3: DBI placeholders and like statement
by runrig (Abbot) on Jun 18, 2013 at 20:16 UTC
    Well, you could say:
    where name like ? || '%'
    But that still won't use an index on the column since the query optimizer doesn't know where any other wildcard will be, whereas hardcoding the entire argument might use an index:
    where name like 'abc%'

      Depending on the DBMS, you could give it an index hint: from tableA (index ndx). You are correct though, most optimizers would simply give it a hard coded selectivity rating which almost always guarantees that an index won't be chosen for that particular column.

      Jason L. Froebe

      Blog, Tech Blog

      where name like ? || '%'
      

      But that still won't use an index [...]

      Well, it depends.

      From PostgreSQL 9.1 onwards, indexes (made by the trigram extension pg_trgm, an extension that comes with postgres) can be used in like searches. (careful, trigram indexes are large, and therefore not always appropriate).

      A test with a 1-column table, filled with 80-characters 'random' strings; 1 million rows. There happened to be two rows that start with 'erix', so I'll use those as search target (i.e.: where txt like 'erix%').

      -- index created like this: create index azjunk6_trgm_re_idx on azjunk6 using gin (txt gin_trgm_ops); -- running a psql session, showing both -- the regular output, and the EXPLAIN: $ psql Timing is on. psql (9.2.4) Type "help" for help. testdb=# prepare ps(text) as select txt from azjunk6 where txt like $1 || '%'; --> $1 is ? in +DBI/DBD::Pg PREPARE Time: 13.785 ms testdb=# execute ps('erix'); txt ---------------------------------------------------------------------- +------------ erixeweq jp ayoyu marqnrjr ughinmfwklvmzxtrfafy mvwf ond wrz lg yso +tkgli ne n erix onka fp ax erk ddcduajmv yrltcfjsfghlt pq cmdfximql jj ckyzhpwhc +q zkakna (2 rows) Time: 54.482 ms testdb=# explain analyze execute ps('erix'); QUERY PLAN ---------------------------------------------------------------------- +------------------------------------------------------------- Bitmap Heap Scan on azjunk6 (cost=108.78..484.93 rows=100 width=81) +(actual time=48.901..48.913 rows=2 loops=1) Recheck Cond: (txt ~~ 'erix%'::text) Rows Removed by Index Recheck: 16 -> Bitmap Index Scan on azjunk6_trgm_re_idx (cost=0.00..108.75 ro +ws=100 width=0) (actual time=48.882..48.882 rows=18 loops=1) Index Cond: (txt ~~ 'erix%'::text) Total runtime: 48.957 ms (6 rows) Time: 49.714 ms -- look mum no hints testdb=#

      ( And yes, it is faster: with the index removed, it takes 147.060 ms )

      Even better: PostgreSQL 9.3 (in beta ATM) lets trgm index on regexen.

Re^3: DBI placeholders and like statement
by Tanktalus (Canon) on Jun 18, 2013 at 23:02 UTC

    Don't worry about it. First time I tried using like with placeholders I did the same thing. :)

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (11)
As of 2014-09-23 11:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (219 votes), past polls