Обсуждение: PHP PDO->bindValue() vs row execute speed difference
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 ?
Is there anything I can do in tuning the DB or just to skip/rewrite PHP PDO ?
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 ?
Is there anything I can do in tuning the DB or just to skip/rewrite PHP PDO ?
2010/11/1 Georgi Ivanov <georgi.r.ivanov@gmail.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