PHP运行查询的时间比MySQL客户端长90倍

I'm running a MySQL query via a command-line PHP script (prepared query using PDO on the mysqlnd driver). It's a simple query with a single left-join, returning 100 rows and 7 small columns per row.

我正在通过命令行PHP脚本运行MySQL查询（在mysqlnd驱动程序上使用PDO准备好的查询）。这是一个简单的查询，只有一个左连接，每行返回100行和7个小列。

When I run this query in the MySQL CLI (on the same machine running the PHP script in question), it takes 0.10 seconds -- even with the SQL_NO_CACHE flag thrown in.

当我在MySQL CLI中运行此查询时（在运行相关PHP脚本的同一台机器上），它需要0.10秒 - 即使抛出了SQL_NO_CACHE标志。

When I run this query, prepared, through PDO, it takes over 9 seconds. This is execute() only -- not including the time it takes for the fetch call.

当我运行此查询时，通过PDO准备它需要超过9秒。这只是execute（） - 不包括获取调用所花费的时间。

An example of my query:

我的查询示例：

SELECT HEX(al.uuid) hexUUID, al.created_on,
    IFNULL(al.state, 'ON') actionType, pp.publishers_id publisher_id,
    pp.products_id product_id, al.action_id, al.last_updated
FROM ActionAPI.actionLists al
LEFT JOIN ActionAPI.publishers_products pp
    ON al.publisher_product_id = pp.id
WHERE (al.test IS NULL OR al.test = 0)
    AND (al.created_on >= :since OR al.last_updated >= :since)
ORDER BY created_on ASC
LIMIT :skip, 100;

I don't believe the query is at fault, considering every native MySQL client I've tried has run it near-instantly, but here's the EXPLAIN for kicks:

我不相信查询是错误的，考虑到我尝试过的每个原生MySQL客户端都在近乎即时运行它，但这里是探索的解释：

+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys           | key        | key_len | ref                               | rows | Extra       |
+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
|  1 | SIMPLE      | al    | index  | created_on,last_updated | created_on | 8       | NULL                              |  100 | Using where |
|  1 | SIMPLE      | pp    | eq_ref | PRIMARY                 | PRIMARY    | 4       | ActionAPI.al.publisher_product_id |    1 |             |
+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
2 rows in set (0.00 sec)

What in the world is PDO doing that is taking 8.9 seconds?

PDO正在做什么，这需要8.9秒？

EDIT: As stated in the comments, I've written a mysql_query version of this as well, and it has the same poor performance. Removing part of the WHERE clause, however, makes it run as fast as the MySQL client. Read on for mind-boggling details.

编辑：正如评论中所述，我也写了一个mysql_query版本，它具有相同的糟糕性能。但是，删除部分WHERE子句会使其运行速度与MySQL客户端一样快。请继续阅读，了解令人难以置信的细节。

4 个解决方案

#1

I had the same problem. Same query was acting differently when launched from cli and from PHP. Explain in cli mentioned correct index usage, in PHP there was nothing. As I have found, the problem was type casting, in my case it was datetime. After I have specifically cast type for compared value eg. where datetime_column > cast('2014-01-12 12:30:01' as datetime) everything works.

我有同样的问题。从cli和PHP启动时，相同的查询行为不同。在cli中解释提到正确的索引用法，在PHP中没有任何内容。正如我所发现的，问题是类型转换，在我的例子中是datetime。在我为比较值专门投射类型后，例如。其中datetime_column> cast（'2014-01-12 12：30：01'作为datetime）一切正常。

#2

Giving a very belated update on this question:

在这个问题上给出一个非常迟来的更新：

I've not found the cause, but it turns out the EXPLAIN was different in PHP versus on the CLI. I'm not sure if any aspect of the connection would cause MySQL to choose to use a different field for the index, because as far as I know those things shouldn't be related; but alas, PHP's EXPLAIN showed that the proper index was not being used, while the CLI's did.

我没有找到原因，但事实证明，EXPLAIN在PHP上与CLI不同。我不确定连接的任何方面是否会导致MySQL选择为索引使用不同的字段，因为据我所知，这些事情不应该相关;但是，PHP的EXPLAIN显示正确的索引没有被使用，而CLI则没有。

The solution in this (baffling) case is to use index hinting. See the 'FROM' line in this modified query from my example:

这个（令人困惑的）案例的解决方案是使用索引提示。从我的示例中查看此修改查询中的“FROM”行：

SELECT HEX(al.uuid) hexUUID, al.created_on,
    IFNULL(al.state, 'ON') actionType, pp.publishers_id publisher_id,
    pp.products_id product_id, al.action_id, al.last_updated
FROM ActionAPI.actionLists al USE INDEX (created_on)
LEFT JOIN ActionAPI.publishers_products pp
    ON al.publisher_product_id = pp.id
WHERE (al.test IS NULL OR al.test = 0)
    AND (al.created_on >= :since OR al.last_updated >= :since)
ORDER BY created_on ASC
LIMIT :skip, 100;

Hope this helps someone!

希望这有助于某人！

#3

PDO uses resources to manipulates row results. Par that with an interpreted language (PHP) and you will have script that takes longer to process than it does for MySQL to return your results.

PDO使用资源来操作行结果。与解释性语言（PHP）相比，您将拥有比MySQL更长时间处理脚本以返回结果的脚本。

NOTE: Using mysql_select_db() or mysqli_select_db() is much faster than PDO.

注意：使用mysql_select_db（）或mysqli_select_db（）比PDO快得多。

To learn more about faster PHP Queries, see: PHP: What's the fastest way to query MySQL? Because PDO is painfully slow

要了解有关更快的PHP查询的更多信息，请参阅：PHP：查询MySQL的最快方法是什么？因为PDO非常缓慢

#4

When your connecting on the command line its VERY likely to be using a different charicter set that when your connecting with PHP.

当您在命令行上进行连接时，很可能使用不同的charicter设置，当您使用PHP连接时。

When your asking it to use an index, It will and because the char sets are close enough to not cause an issue (at a guess ? It depends on your set up of the table and columns)

当你要求它使用索引时，它会因为char集足够接近而不会导致问题（猜测？这取决于你对表和列的设置）

Try thowing some unicode charicters in there and it will likely start to return bad results.

尝试在那里打一些unicode charicters，它可能会开始返回糟糕的结果。

Make sure the the char set matches on the connection, the table and the column for best results. If They cant be done, The connection is the most important

确保char集在连接，表和列上匹配，以获得最佳结果。如果他们无法完成，那么连接是最重要的

UTF-8 vs Latin1 mysql, indexes not used on utf-8

UTF-8 vs Latin1 mysql，未在utf-8上使用的索引

Has some more info

有更多的信息

#1

#2