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.

Just for kicks, would you mind running it with `mysql_query()` one time to see if it's a `PDO` thing? — David, Sep 09 '12 at 04:13
`mysqli_query()`, `mysql_query()` doesn't exist any more since YEARS! — Flavius, Sep 09 '12 at 04:15
Again, "just for kicks." It's just to see if it's a PDO thing. — David, Sep 09 '12 at 04:16
No, you know it's just as slow as a mysql binding which we know it was heavily unoptimized and it's not maintained any longer. Using mysql could lead to more confusion, if you have programmed for long enough, you know how to isolate problems. — Flavius, Sep 09 '12 at 04:22
Fair question, either way. I've written a test for mysql_query as well, and it performs just as slowly as the PDO method. — Tom Frost, Sep 09 '12 at 04:27
Can you try to remove parts of the query (one of the WHERE clauses, the ORDER, the LIMIT, HEX/ISNULL result fields) one by one and see if that makes a difference? — DCoder, Sep 09 '12 at 04:32
Good call, DCoder. Changed the final WHERE clause from created_on >= :since OR last_updated >= :since to just created_on >= :since, and now it runs at light speed. That second condition is essential, though. Crazy that both PDO and mysql_query would hang up there when native MySQL doesn't. — Tom Frost, Sep 09 '12 at 04:45
That's interesting! Can you now try `created_on >= :since1 OR last_updated >= :since2` and set these both to the same value? Do you have an index covering both `created_on` and `last_updated` columns? — DCoder, Sep 09 '12 at 04:50
Indexes are on both of those fields, and setting them to the same date (even hardcoding them in my mysql_query test version) results in the same slowdown. My brain is beginning to melt. — Tom Frost, Sep 09 '12 at 04:52
Reading that removing the last condition in the WHERE clause resolves your issue leads me question whether PDO is manipulating the query before being sent to the optimizer. Can you try last_updated and created_on individually? — Kermit, Sep 09 '12 at 04:58
Keep in mind that this is happening in mysql_query too. With that said, though, you're on to something. last_updated is generally NULL for any row that hasn't been updated since creation. If I remove the created_on condition and keep just the last_updated condition, the query gets slow again. However, that version of the query STILL runs instantly on the MySQL CLI. — Tom Frost, Sep 09 '12 at 05:05
Can you recreate your schema and some sample data at http://sqlfiddle.com/ ? — DCoder, Sep 09 '12 at 05:06
I'm out of time for tonight, but I've thrown a couple quick CREATE TABLE queries up to give you an idea of the schema: http://sqlfiddle.com/#!2/55fd0 — Tom Frost, Sep 09 '12 at 05:16
Sorry, that schema doesn't quite match the query you're running (`state` and `test` columns are missing), but after correcting that and generating some random data I am unable to reproduce the problem locally. Try enabling [MySQL Profiling](http://dev.mysql.com/doc/refman/5.0/en/show-profile.html) and seeing how long it actually takes MySQL to run this query - if the time spent there is significantly different from total time, you know the fault is on PHP's side... (Which version of PHP are you running, which OS?) — DCoder, Sep 09 '12 at 06:12

Tom Frost · Accepted Answer · 2012-11-12T15:17:58.933

7

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!

edited Nov 12 '12 at 15:17

answered Nov 12 '12 at 14:46

Tom Frost

1,015
11
21

At a guess its your connection char type settings not maching the table or columns char type. Mysql Sometimes igores the index when its doesnt think it can use the index because of the char set differences. – exussum Jan 24 '14 at 17:35
How do you use PHP's EXPLAIN? I can't find a reference in the PDO manual. Do you just manually add 'explain' to the start of the query, or is there a PDO::explain function somewhere that I can't find? – Benubird Feb 10 '15 at 09:35
2

@Benubird: Yep, I just threw an 'EXPLAIN' in front of the SELECT and dumped out the results. – Tom Frost Feb 11 '15 at 15:47

Miisha · Answer 2 · 2015-11-26T09:03:16.403

7

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.

edited Nov 26 '15 at 09:03

answered Jan 24 '14 at 17:31

Miisha

116
1
4

score 1 · Answer 3 · edited May 23 '17 at 12:32

1

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.

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

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

edited May 23 '17 at 12:32

Community

1
1

answered Sep 09 '12 at 04:23

Todd Moses

10,969
10
47
65

3

Absolutely, I'd expect PDO to run slower than MySQL. But I've been programming with PHP/MySQL for *years*, and an overhead of 90x the query time for such a simple result set is far, far more than what can be explained away by the interpreted language layer. There's got to be something more going on here. – Tom Frost Sep 09 '12 at 04:30
Well, I guess the idea is that `mysqli` can't hurt, in response to @Flavious in the OP's comment thread. – David Sep 09 '12 at 04:41

score 0 · Answer 4 · edited May 23 '17 at 12:06

When your connecting on the command line its VERY likely to be using a different charicter set that when your connecting with 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)

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

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

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

Has some more info

PHP takes 90x longer to run query than MySQL client

4 Answers4

Linked