0

With PHP, I am trying to execute many queries with PDO in for loop and I want to make the DB optimizer does the soft parsing.

I am binding parameters now but I am not sure if the optimizer really uses the soft parsing.

The sample query

select * from table as t where t.id = :id ;

PHP code

$query = "select * from table as t where t.id = :id ;"
$pdo->prepare($query);
$id = 0;
$query->bindParam(':id', $id, PDO::PARAM_INT);
for($id; $id<10; $id++){
    $query->execute()
}
Gayan S. Muthukumarana
  • 902
  • 1
  • 11
  • 28
체라치에
  • 173
  • 1
  • 16

1 Answers1

1

Not sure I understand what a soft parsing is but a query in your case gets parsed only once which is probably what you are asking about.

A couple notes.

  1. You need to make sure that the emulation mode is turned off.
  2. With such a simple query you won't likely notice any performance improvement. Parsing is really fast nowadays.
  3. Although I understand that your query is just an example, nevertheless, instead of running a SELECT a query in a loop it will be better gather all ids into IN() clause in a single query or use a JOIN if applicable.
  4. running multiple similar queries is rather justified with DML statements. In such a case, if your concern is performance, I would rather advise to wrap all queries in a transaction, it could greatly improve the speed in some circumstances
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • Thank you for your answer :) My query is actually very long and I joined many tables on it.. and my customers search many kinds of information on my website using the query. so I was curious. – 체라치에 Oct 10 '19 at 08:36
  • @체라치에 but you don't run a search query in a loop so I am at loss what is your question really about – Your Common Sense Oct 10 '19 at 08:39
  • They send me many similar queries like this (this one is very simplified for an example) "select * from table_a a, table_b b where a.id = 2 and b.dt >= '2019-10-01' and b.dt <= '2019-10-10' and a.id = b.id;" so I was curious if I write the query and bind parameters from customers' input then execute in PHP PDO like this "select * from table_a a, table_b b where a.id = :id and b.dt >= :start_dt and b.dt <= :end_dt and a.id = b.id;" so my question is.. In this way, will the optimizer use query_cache? or just hard parse it again and again.. – 체라치에 Oct 10 '19 at 08:58
  • This is **entirely** different question. – Your Common Sense Oct 10 '19 at 09:00
  • that's why I mentioned soft parse.. okay I will open another question. – 체라치에 Oct 10 '19 at 09:07
  • The Query Cache is _not_ involved. It comes into play only when the _identical_ query is posted twice without any changes to the table meanwhile. – Rick James Oct 17 '19 at 00:26
  • Binding is a very small part of the execution time. – Rick James Oct 17 '19 at 00:27