Do MySQL prepared queries provide a performance benefit for once-per-session queries?

Question

According to the documentation, a prepared query provides a significant performance benefit if you're running a query multiple times because the overhead of the MySQL server parsing the query only happens once. I'm wondering what exactly they mean by "multiple times" there.

I.e., say you have a web page that runs a query one time. Now say that page gets called 50 times per second. Does it make more sense from a performance standpoint to prepare() the query (thereby requiring two roundtrips to the DB server; one to prepare the query, one to run it) or to just send the query normally (which only requires one roundtrip)? Is MySQL and/or the PHP mysqli driver smart enough to realize when a query was prepare()'d in a previous invocation?

Answer 1

No. PHP is a "shared nothing" architecture, so every resource associated with one request (one page view) is discarded at the end of that request. Prepared queries are not available to a subsequent database connections.

The scenario in which you would get benefit from a prepared query is when you prepare it and execute it many times during the same PHP request.

Answer 2

"-1 because allanc asked about prepared queries, not stored procs, you can't make a general statement that procs are always faster, and there's nothing about a proc that makes it more resistant to SQL injection"

according to the mysqli manual (http://us3.php.net/manual/en/mysqli.prepare.php), you can cast the types that are allowed in prepared statements. From php.net:

$city = "Amersfoort";

/* create a prepared statement */ if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {

/* bind parameters for markers */ $stmt->bind_param("s", $city);

in this example, the "s" stands for string. That means that only a string is an acceptable input, unless I'm misunderstanding things. Anyways, that seems like it would counteract a style of sql injection I'm familiar with. at least it should provide a "more resistant" query, shouldn't it?

Answer 3

I should write a larger response to clarify my comment to TravisO's answer. I'll mark this as a wiki response.

Using query parameters instead of interpolating unvalidated data into an SQL query string helps protect against SQL injection. This is true whether you run queries in your application code, or in a stored procedure.

CREATE PROCEDURE safeproc(arg1 VARCHAR(10))
BEGIN
  SET @sql = 'SELECT * FROM mytable WHERE string = ?';
  PREPARE stmt1 FROM @sql;
  EXECUTE stmt1 USING @arg1;
END

But this is orthogonal to the use of stored procedures. One can just as easily use variables in the proc in an unsafe way, interpolating them into a query and introducing a risk of SQL injection.

CREATE PROCEDURE unsafeproc(arg1 VARCHAR(10))
BEGIN
  SET @sql = CONCAT('SELECT * FROM mytable ORDER BY ', arg1);
  PREPARE stmt1 FROM @sql;
  EXECUTE stmt1;
END

My point is that using a stored procedure is no more and no less safe than using dynamic SQL in application code. In both environments, if you interpolate unvalidated data into the SQL syntax, you risk SQL injection.