Is it better to use a prepared Select statement when you are only doing one select?

Question

I am currently writing a CRUD class in PHP using PDO.

I like the security that prepared statements provide, but I have heard that they also prevent databases like mysql from using the queryCache.

Is it better to use a prepared Select statement when you are only doing one select at a time? or would just $pdo->quote() suffice the security standpoint (or have any other advantages like caching?).

All my update, delete and inserts are done using prepared statements already. I am just curious about the selects.

Answer 1

Yes, use prepared statements. I seriously doubt you will run into performance problems with prepared statements running much slower than just a regular literal query. However, on mysql, you appear to be correct. I would opt for prepared statements nevertheless.

Here is one reference: http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/

Although, if you are worried about caching, you might want to look at things like memcached.

Answer 2

This is my understanding, as confirmed by discussion from: here

A normal query is taken as a single string, parsed, executed, and returned. End of story. A prepared statement is taken as a template string, parsed, and cached. It then has variables passed into it, almost like a function call.

Caching the query once tends to cost a little bit more than just executing it straight. The savings come in later calls, when you skip the compilation step. You save per repeated query the amount of the compilation.

So, in short, on MySQL, if you're executing a query once, preparing it just adds a needless extra amount of processing.

Answer 3

Prepared statements are generally considered to be better practice.

I would suggest reading the MySql article on prepared statements and their practicalities and advantages over conventional plain-vanilla interpolated stringy queries.

Answer 4

Are you only doing a select "once" in the application lifetime, or "once" per call to the function?

Because if the latter, you should still benefit from the caching in the prepared statement anyway.

Answer 5

Just a reminder that MySQL > 5.1.17 does use the query cache for prepared statements.

From the code POV, i believe prepared statements are, for the most part, the way to go in terms of readability, maintainability, etc...

The one reason not to use them would be expensive queries that get called with some frequency. (queries that take a lot of time to run and have a real benefit on being on the query cache).

Answer 6

MySQLPerformanceBlog.com did some benchmarks in an article about "Prepared Statements." Peter Zaitsev wrote:

I’ve done a simple benchmark (using SysBench) to see performance of simple query (single row point select) using standard statement, prepared statement and have it served from query cache. Prepared statements give 2290 queries/sec which is significantly better than 2000 with standard statements but it is still well below 4470 queries/sec when results are served from query cache.

This seems to say that the "overhead" of using prepared statements is that they are 14.5% faster than using a straight query execution, at least in this simple test. The relative difference probably diminishes with a more complex query or a larger result set.

It seems counter-intuitive that prepared queries would be faster, given the double round-trip to the server and other factors. Peter's benchmark lacks details. Anyway, you should run your own tests, because the type of query you run, and your environment and hardware, are definitely important factors.

As for Query Cache, it was true in the past that prepared statements were incompatible with caching query results, but this was changed. See "How the Query Cache Operates" in the MySQL documentation:

Before MySQL 5.1.17, prepared statements do not use the query cache. Beginning with 5.1.17, prepared statements use the query cache under certain conditions, which differ depending on the preparation method: ...

The documentation goes on to describe these conditions. Go read it.

I do recommend using prepared statements for SELECT queries. Quoting variables as you interpolate them into SQL statements can be effective if you do it consistently. But even quoting may have some subtle security vulnerabilities, e.g. with multi-byte character sets (see MySQL bug #8378). It's easier to use prepared queries in a secure way in these cases.