Should I Use Prepared Statements for MySQL in PHP Performance-Wise

Should I use prepared statements for MySQL in PHP PERFORMANCE-WISE?

The History

This was my first Stackoverflow answer. A lot has changed since, specially the deprecation and removal of the mysql API. Even if you are still on php 5.6, the mysql_* api should not be used. Now PDO or mysqli are the only options to choose. PDO is better to lots of reasons.

Are prepared statements cached across page loads?

I've read some conflicting reports about what PHP's mysqli or PDO
libraries do? Do either of them cache the prepared statement across
script execution?

The same prepared statement will not be used in between page loads. It has to be prepared every time. If squeezing every large millisecond matters, a stored procedure might be a good idea (assuming you have a complicated query).

For large inserts (thousands of rows) A bigger boost can probably be gained by dumping your data into a text file and loading it with LOAD DATA IN FILE . It's a lot faster than a series of inserts.

The original answer

The truth of the matter is that sometimes mysqli is faster and at other times mysql api is faster. But the difference is really really small. If you look at any of the performance tests on the web the difference is really just 10 - 20 milliseconds. The best way to boost performance is to optimize table design.

Many of the tests that 'prove' the older api to be faster conveniently forget that for maximum security mysql_real_escape_string() should be called for each variable used in the query.

Queries are cached by the server, if and only if the data on all the tables that are used in the query have remained unchanged.

Await another update with actual numbers

Should I always prefer working with prepared SQL statements, for performance benefits?

Prepared Statements:

Why use prepared statements?

There are numerous advantages to using
prepared statements in your
applications, both for security and
performance reasons.

Prepared statements can help increase
security by separating SQL logic from
the data being supplied. This
separation of logic and data can help
prevent a very common type of
vulnerability called an SQL injection
attack. Normally when you are dealing
with an ad hoc query, you need to be
very careful when handling the data
that you received from the user. This
entails using functions that escape
all of the necessary trouble
characters, such as the single quote,
double quote, and backslash
characters. This is unnecessary when
dealing with prepared statements. The
separation of the data allows MySQL to
automatically take into account these
characters and they do not need to be
escaped using any special function.

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, such as when doing
many INSERT statements.

(Note: While it will not happen with
MySQL 4.1, future versions will also
cache the execution plan for prepared
statements, eliminating another bit of
overhead you currently pay for each
query execution.)

The second place where performance may
increase is through the use of the new
binary protocol that prepared
statements can use. The traditional
protocol in MySQL always converts
everything into strings before sending
them across the network. This means
that the client converts the data into
strings, which are often larger than
the original data, sends it over the
network (or other transport) to the
server, which finally decodes the
string into the correct datatype. The
binary protocol removes this
conversion overhead. All types are
sent in a native binary form, which
saves the conversion CPU usage, and
can also cut down on network usage.

When should you use prepared statements? Prepared statements can
be useful for all of the above
reasons, however they should not (and
can not) be used for everything in
your application. First off, the type
of queries that they work on is
limited to DML (INSERT, REPLACE,
UPDATE, and DELETE), CREATE TABLE, and
SELECT queries. Support for additional
query types will be added in further
versions, to make the prepared
statements API more general.

-> Sometimes prepared statements can actually be slower than regular
queries. The reason for this is that
there are two round-trips to the
server, which can slow down simple
queries that are only executed a
single time. In cases like that, one
has to decide if it is worth trading
off the performance impact of this
extra round-trip in order to gain the
security benefits of using prepared
statements.

Should you use prepared statements for their escaping only?

The difference considered to be negligible.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.

The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

• prepared statement makes proper formatting (or handling) inevitable.
• prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
  
  
  • some PHP 'magic' feature which rather spoils the data than make it safe.
  • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
• prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
• prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

Should I use prepared statements for static values?

Save yourself the trouble and just use query. No need to use prepare statement in that situation, unless taking in a variable.

MySQL prepared statement vs normal query. Gains & Losses

Normally, if you just use a prepared statement in place of a plain query, it's marginally slower since the query is prepared and executed in two steps instead of one. Prepared statements become faster only when you're preparing the statement and then executing it multiple times.

However, in this case you're using mysql_real_escape_string, which does a roundtrip to the database. Even worse, you're doing it inside a loop, so, executing it multiple times per query. So, in this case replacing all of those roundtrips with a single prepared statement is a win-win-win.

Regarding your last question, there's no reason you can't use the same query with a prepared statement as you would through the normal query parser (i.e. no reason to execute one version with an IN and the other with a bunch of ORs). The prepared statement can have IN (?, ?, ?), and then you just bind that number of parameters.

My advice would be to always use prepared statements. In cases where they add a marginal performance overhead, they're still worth it for the security (no SQL injection) and readability benefits. For sure, anytime you find yourself resorting to mysql_real_escape_string, you should use a prepared statement instead. (For simple one-off queries where there's no need to escape variable inputs, they aren't strictly necessary.)

When should I use prepared statements exactly?

The straight answer is: yes, you can.

The reason why is actually up to you, since it's anyway good practice to use prepared statement whenever you pass values.

Also consider that, if you are not binding any parameter, it makes more sense to use the query() method, just to be explicit on the fact that you are not going to bind anything. So your second query would be

$stm1 = $db->query("UPDATE user SET rep = rep + 15 WHERE id = $author_id");

(see http://php.net/manual/en/pdo.query.php)

instead of

$stm1 = $db->prepare("UPDATE user SET rep = rep + 15 WHERE id = $author_id");
$stm1->execute();

Moreover you mentioned a dynamic query, but this is not the case of your sample code. Anyway I will give you an example of how two use prepared statement also on queries dynamically generated.

It's a silly example, but should be enough to give you an idea.

Assume we have some values to update 'email', 'date_of_birth' and 'website'. Let's say we want to do some check on this data before inserting them. I'll pretend we have a valid() function already in place.

    $dynamic_sql = array();
    $parameters[':date_of_bird'] = $date_of_birth;

    if(valid($email)) {
        $dynamic_sql['email_sql'] = "email = :email";
        $parameters[':email'] = $email;

    }
    if(valid($website)) {
        $dynamic_sql['website_sql'] = "website = :website";
        $parameters[':website'] = $website;
    }

    if(count($dynamic_sql)>0) {
        $dynamic_sql = ','.implode($dynamic_sql);
    }

    $query = "UPDATE user
    SET date_of_birth = :date_of_birth $dynamic_sql
    WHERE
    user_id = :user_id";

    $stm = $db->prepare($query);
    $stm->execute($parameters);

This kind of approach will allow you to keep using prepared statement also with dynamically generated SQL.

Prepared Statements - Should I use php prepared statements even in queries without (?) placeholders?

If you are running a query without any user-entered variables, you can just do:

$db->query("SELECT name FROM contact")

As soon as you start entering in user-inputted data, then you need to use a prepared statement.

$db->prepare("SELECT phone FROM contact WHERE name = ?");

PHP/MySQL Prepared Statements - Can one user benefit from another user's prepared query?

It depends on which version of MySQL you are using. See this page : http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
If you are using MySQL before version 5.1.17, prepared statements won't be cached, and the use of MySQL query cache gives better results than the use of prepared statements, so you'd better worry about whether you are using this cache in an optimized way or not.
Otherwise, I think the answer to your question is yes, because instead of doing this twice:

1. send the request to the server
2. compile the request
3. execution plan
4. execute request
5. send result back

you would do this:

PREPARE :

1. send the request to the server
2. compile the request
3. execution plan
4. store the compiled request in memory
5. return a request identifier

EXECUTE with first series of parameters:

1. ask for execution with the identifier
2. execute
3. return result

EXECUTE another series of parameters:
1. ask for execution with the identifier
2. execute
3. return result

(all this was translated from this french course on prepared statements)
As you see, you'll have a real performance gain if:

• you execute this request a lot
• the request is complex and takes time to compile
• the query cache is used

---

Related Topics