Is it better to use a prepared Select statement when you make only one choice?

Question

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

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

Is it better to use a prepared Select statement when you make only one choice at a time? or would just $ pdo-> quote () be sufficient for a security point of view (or have any other advantages such as caching?).

All my updates, deletions, and pastes are done using prepared statements. I'm just curious to choose.

Answer 1

MySQLPerformanceBlog.com made some guides in the article " Prepared Reports ." Peter Zaitsev wrote:

I did a simple test (using SysBench) to see the performance of a simple query (single line selection), using a standard ticket, a prepared statement, and it is served from the query cache. Prepared statements give 2290 queries / sec, which is much better than 2000 with standard ones, but it is still significantly lower than 4470 queries / sec when the results are from the query cache.

It seems that the "overhead" of using prepared statements is that they are 14.5% faster than using direct query execution, at least in this simple test. The relative difference is likely to decrease with a more complex query or a larger set of results.

It seems controversial that prepared queries will be faster, given double server feedback and other factors. There are no details in Peter’s test. In any case, you should run your own tests, because the type of request that you run, as well as your environment and equipment, are definitely important factors.

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

Before MySQL 5.1.17 prepared statements do not use the query cache. Starting from 5.1.17, prepared statements use the query cache under certain conditions, which vary depending on the preparation method: ...

The following describes these conditions. Go read it.

I recommend using prepared statements for SELECT queries. Quoting variables when interpolating them into SQL statements can be effective if you do this sequentially. But even quoting can have some subtle security vulnerabilities, for example. with multibyte character sets (see MySQL Error # 8378 ). In these cases, it is easier to use prepared queries in a safe way.

Answer 2

Yes, use the prepared instructions. I seriously doubt that you will run into performance issues when prepared statements work much slower than just a regular literal. However, in mysql you look right. Nevertheless, I would choose prepared statements.

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

Although, if you are concerned about caching, you can look at things like memcached .

Answer 3

This is my understanding, as evidenced by the discussion: here

A regular query is taken as a single line, parsed, executed and returned. The end of the story. A prepared statement is taken as a string, parsing, and caching template. Then it has variables in it, almost like a function call.

Caching a request once results in a little more cost than just executing it directly. Savings come later calls when you skip the compilation step. You save the compilation amount for each repeated request.

So, in short, in MySQL, if you execute a query once, preparing it just adds unnecessary extra processing.

Answer 4

Prepared statements are generally considered best practice.

I would suggest reading a MySql article on prepared statements and their practical capabilities and benefits over regular simple vanilla interpolated strict queries.

Answer 5

Do you only make the choice “once” in the life of the application or “once” for calling a function?

Because, if the latter, you should still use caching in the prepared statement.

Answer 6

Recall that MySQL> 5.1.17 uses the query cache for prepared statements.

From the POV code, I believe that prepared statements, for the most part, relate to readability, maintainability, etc.

One reason not to use them is costly requests that are invoked at some frequency. (queries that take a long time to run and have real benefits of using the query cache).