Shouldn't prepared statements be much faster?

Question

$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?

Answer 1

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.

Answer 2

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.

Answer 3

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