Do I need to persist a prepared statement?

Question

I have the following code:

public function getDefinitions($wordID) {
    $query = $this->dbc->prepare('SELECT * FROM definitions WHERE wordID = ?');

    $query->bind_param('i', $wordID);
    $query->execute();

    // ...

    $query->close();

    return $result;
}

It would seem that this would recreate the prepared statement for each invocation. That does not seem to take advantage of the full benefit of prepared statements. Especially in the case these prepare statements were stored server side. Is that true?

If so, should I store the prepare statement (in this case as a property) to persist it between invocations. Is there a way to persist the prepared statement between requests? I assume that's essentially a stored procedure?

Currently I am using MySQLi. Please note any API difference if I were using another driver (e.g. PDO).

Answer 1

should I store the prepare statement (in this case as a property) to persist it between invocations.

I wouldn't say you "should". You may, if you foresee a lot of consequent invocations (which you cannot group up to call at once). Either way, you scarcely will be able to notice the real life difference.

Is there a way to persist the prepared statement between requests?

In PHP - no. Prepared statements are connection-based, connection is closed along with request, and persistent connections won't help you either, as mysqli is using designated mechanism to clean connection state before reuse.

Please note any API difference if I were using another driver (e.g. PDO).

PDO has an advantage over mysqli as it can emulate prepared statements, eliminating even such a negligible overhead of two database calls, which makes it more suitable for the real life usage (beside other key benefits).

In a nutshell, the "prepare once-multiple execute" feature of native prepared statements can have noticeable effect only for a huge number of queries, starting from thousands.

Answer 2

You should persist the query if you plan on using it again.

As you are using mysqli prepared statements should always be real prepared statements. (In contradiction to PDO, which emulates queries based on the used driver and might not send an additional database call). The data replacing the placeholders is then sent in a different pack of data. A nativly-prepared statement takes therefore two trips to the database if only issued once. Sending this extra action takes time. If you know that you only want to issue the query once, it might in certain circumstances be positive to use traditional queries. (However, this can only be advisable if you are absolutly sure about the potential negative security implications standard queries have in comparison to prepared statements. The added level safety using prepared statements outwights the benefit such a change may have in most cases.)

However, if you want to re-run the query preparing it is the right approach. MySQL is supposed to cache converted statements, so the php-site is the only way of optimizing and reusing.

What you can do is to prevent the additional call to the prepare method and the action it may trigger in the low-level database connector by preserving the statement in the method using a static variable or a property. The impact on most real applications is however arguable.