From: | Cédric Villemain <cedric(dot)villemain(dot)debian(at)gmail(dot)com> |
---|---|
To: | Georgi Ivanov <georgi(dot)r(dot)ivanov(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PHP PDO->bindValue() vs row execute speed difference |
Date: | 2010-11-01 14:29:38 |
Message-ID: | AANLkTinmN24Dktgk11pFcABVRC_TiqWMUR0xVGXsn=HU@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2010/11/1 Georgi Ivanov <georgi(dot)r(dot)ivanov(at)gmail(dot)com>:
> Hi,
> I have following situation:
> $q = 'select * from tbl1 where id = :id';
> $stmt = $dbh->prepare($q);
> $stmt->bindValue(':id', $id , PDO::PARAM_INT);
> $stmt->execute();
> //1000 ms
> and
>
> $q1 = ' select * from tbl1 where id = 100 ';
> $stmt = $dbh->prepare($q);
> //NO binding here !
> $stmt->execute();
> //2 ms
>
> The queries are a bit more complex, but this is enough to get the idea.
>
> So the first query runs for about 1000 ms
>
> The second query( w/o binding) runs for about 2 ms.
> If I'm correct, the first query is interpreted as : select * from tbl1 where
> id = (INT )
> and I don't get good execution plan.
>
> The second one is fast, because the DB see the literal 100 as value for ID
> and makes a better execution plan.
> Am I correct in my thoughts ?
Yes. But usualy for a PK there is no trouble and planner should use index.
you can give a try with psql 'prepare foo ... ; explain execute
foo(100); ' vs 'explain select .... where id = 100'
> Is there anything I can do in tuning the DB or just to skip/rewrite PHP PDO
> ?
Once you have the explain output for the named prepared statement, you'll know.
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2010-11-01 14:33:39 | Re: async queries in Perl and poll()/select() loop - how to make them work together? |
Previous Message | Georgi Ivanov | 2010-11-01 14:21:25 | PHP PDO->bindValue() vs row execute speed difference |