PHP takes 90x longer to run query than MySQL client

Question

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.

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.

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.

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:

+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
| 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?

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.

Answer 1

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.

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

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!

Answer 2

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.