PDO in mysql performance

Question

Recently I was going through a blog and noticed some points about using PDO in mysql and it changed my view about the PDO. The points are :

1. Native prepared statements cannot take advantage of the query cache, resulting in lower performance.

2. Native prepared statements cannot execute certains types of queries, like "SHOW TABLES"

3. Native prepared statements don't correctly communicate column lengths for certain other "SHOW" queries, resulting in garbled results.

4. Calling stored procedures multiple times using native prepared statements causes the connection to drop.

Can anyone comment on this please?

I wanted query cache in my web app. I am preparing to move my web app to make use of PDO after considering performance issues with my website. Can anyone please suggest me?

Thanks in advance.

Answer 1

Well

• you are right for the first point
• The SHOW and SHOW TABLES are commands not commonly used in most use cases
• Iam using prepared statemends in combination with stored procedures and have not yet experienced such problems.

You may use the following command to enable query caching:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

This command is available from PHP 5.1.3 on only.

Answer 2

Prepared statements DO use query cache, but it does have conditions:

• http://bugs.mysql.com/bug.php?id=735
• http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html
• http://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

From the 5.1 Doc:

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:

• Statements that are issued using the binary protocol using mysql_stmt_prepare() and mysql_stmt_execute().
• Statements that are issued using the text (nonbinary) protocol using PREPARE and EXECUTE. See Section 12.6, “SQL Syntax for Prepared Statements”.

Which the 5.5 Doc restates as:

Prepared statements that are issued using the binary protocol using mysql_stmt_prepare() and mysql_stmt_execute(), are subject to limitations on caching. Comparison with statements in the query cache is based on the text of the statement after expansion of ? parameter markers. The statement is compared only with other cached statements that were executed using the binary protocol. That is, for query cache purposes, prepared statements issued using the binary protocol are distinct from prepared statements issued using the text protocol.