Shouldn't prepared statements be much faster?

$s = explode (" ", microtime());
$s = $s[0]+$s[1];
$con = mysqli_connect ('localhost', 'test', 'pass', 'db') or die('Err');

for ($i=0; $i<1000; $i++) {

  $stmt = $con -> prepare( " SELECT MAX(id) AS max_id , MIN(id) AS min_id FROM tb ");
  $stmt -> execute();
  $stmt->bind_result($M,$m);
  $stmt->free_result();
  $rand = mt_rand( $m , $M ).'<br/>';

  $res = $con -> prepare( " SELECT * FROM tb WHERE id >= ? LIMIT 0,1 ");
  $res -> bind_param("s", $rand);
  $res -> execute();
  $res->free_result();
}

$e = explode (" ", microtime());
$e = $e[0]+$e[1];
echo  number_format($e-$s, 4, '.', '');

// and:

$link = mysql_connect ("localhost", "test", "pass") or die ();
mysql_select_db ("db") or die ("Unable to select database".mysql_error());

for ($i=0; $i<1000; $i++) {
  $range_result = mysql_query( " SELECT MAX(`id`) AS max_id , MIN(`id`) AS min_id FROM tb ");
  $range_row = mysql_fetch_object开发者_如何学编程( $range_result ); 
  $random = mt_rand( $range_row->min_id , $range_row->max_id );
  $result = mysql_query( " SELECT * FROM tb WHERE id >= $random LIMIT 0,1 ");
}

defenitly prepared statements are much more safer but also every where it says that they are much faster BUT in my test on the above code I have: - 2.45 sec for prepared statements - 5.05 sec for the secon example

What do you think I'm doing wrong? Should I use the second solution or I should try to optimize the prep stmt?

---

What you are doing wrong is that you are preparing the statement a thousand times and only run each prepared statement once. You should prepare it once, and run it a thousand times.

---

There's no benefit to preparing a statement inside a loop for a single execution. You're just adding overhead. Use prepared statements for queries you execute repeatedly usually with different parameters.

---

@silversy -

Loop basics 101 (or plain coding 101): Move loop-invariant code out of the loops. Why would you prepare the statement in the loop when it takes no parameters that are dependent on the loop itself?

Of course it's going to suck gonads if you use prepared statements if you prepare them with each loop iteration. The overhead you observe is in the statement being initially prepared rather than in its execution.

Re-do your code as follows, and re-try again:

$stmt = $con -> prepare( " SELECT MAX(id) AS max_id , MIN(id) AS min_id FROM tb ");
$res = $con -> prepare( " SELECT * FROM tb WHERE id >= ? LIMIT 0,1 ");

for ($i=0; $i<1000; $i++) {

  $stmt -> execute();
  $stmt->bind_result($M,$m);
  $stmt->free_result();
  $rand = mt_rand( $m , $M ).'<br/>';

  $res -> bind_param("s", $rand);
  $res -> execute();
  $res->free_result();
}

---

---

The caveat here, though, is that the difference between a prepared statement and a non-prepared one becomes negligible as the computation time to execute the query and fetch the data becomes large.

Just to pull some fictional numbers out of my @@@ for the sake of illustration:

Say that the query itself and the fetching of the data takes 0.01sec (call this A). Also, imagine that the construction and execution of the code behind a prepared statement takes 0.01sec (X) and for the unprepared query 0.05sec or 5 * 0.01 sec (Y = 5 * X). The ration between the prepared and unprepared code would be :

(A + Y)/(A + X) = 0.06sec/0.02sec = 3 -> unprepared execution is three times slower

And let's assume that for another query, the fetching time (due to data volume or network throughput) is 10secs (1000 * 0.01sec). Then the ratio changes

(A + Y)/(A + X) = 10.05sec/10.01sec ~=~ 1.004 

They are pretty much indistinguishable. What I'm trying to say is that yes, prepared statements are faster (and that you should use them), BUT the test you are implementing is not necessarily a good way to find that out or to gauge their value. You have to take other factors into account to really measure the performance you are squeezing out of them.

---

In addition to the answers above...

I see you are using MySQL and below is the link from there as to prepared statements: http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

An excerpt from there:

The increase in performance in prepared statements can come from a few different features. First is the need to only parse the query a single time. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to a speed increase if you need to run the same query many times