phpMyAdmin reports incorrect query time

Question

MySQL 5.5

I am trying improve speed on this query:

SELECT * FROM `core_table` WHERE `id` = '57' AND `model` IN (SELECT `new_models` FROM `raw_table`)

I tried to benchmark it in phpMyAdmin by running:

SELECT SQL_NO_CACHE * FROM `core_table` WHERE `id` = '57' AND `model` IN (SELECT `new_models` FROM `raw_table`)

According to MySQL slow log: Query_time: 1174.17, Rows_sent: 1 Rows_examined: 3006660068

phpMyAdmin says "Query took 0.0024 sec" which is completely false. It took 20 minutes.

Why does phpMyAdmin report an egregiously false value? Is this a fixable bug or should I just ignore phpMyAdmin query times forever?

EDIT 2015-04-30:

It's been awhile and I'm coming back to this because someone finally posted an answer. I seem to remember finding a possible reason for this myself. I believe it was that phpMyAdmin runs a second query, I can't remember for certain which one, and reports the query time for that query instead of for the one it seems to be reporting on. I.E. perhaps even doing a SHOW TABLES query or something like that. But I don't have time to look into it right now.

Ali's answer is not correct, because as I mentioned, the MySQL slow log reports the time as 20 minutes, so this isn't a browser rendering issue.

what do you want to fix? the time of execution being reported or make the query more fasster to execute? — John Woo, Jun 01 '13 at 00:09
I'm focusing on the incorrect query time for now. I realize that that's probably a bit harder to answer. — Buttle Butkus, Jun 01 '13 at 00:10
@ButtleButkus did you found a solution to extract the true time? — user2342558, Sep 28 '21 at 08:42

score 2 · Accepted Answer · answered Jul 13 '15 at 09:19

2

The reported time is not incorrect, but likely to be misunderstood. phpMyAdmin silently executes another query with "SQL_CALC_FOUND_ROWS"; This is what you see in your MySQL slow log: scanning all rows takes 20min, but the result is only 1 row: the result count.

On the result page, it will display the total result count (how should it know without doing the query above?) and the first page of the actual results. After all, phpMyAdmin is quite correct in saying that the query (retrieving the first page!) took only 0.0024s. It simply hides the expensive row-counting query from the user.

answered Jul 13 '15 at 09:19

Pupsi

44
2

2

Yes you are right. But isn't it stupid to make someone wait 20 minutes for the count on a millisecond query? I think this should be considered a bug. – Buttle Butkus Jul 21 '15 at 02:05
The best thing maybe to show the two different times, so let the user to know the real time of the execution and the other time spent to other works... – user2342558 Dec 18 '18 at 11:26
It isn't misunderstood, it is a **BUG** because if it says "Query took ... seconds" and below it displays my SELECT query, it's logic that the user thinks that that amount is about that query, not any another hidden query that phpmyadmin does. – user2342558 Sep 28 '21 at 08:18

score -1 · Answer 2 · answered Apr 19 '15 at 10:32

-1

The query time in phpMyAdmin is only the time that database engine extracts result from database. If you send your query from webpage the Database should parse result into localhost and then serve it to your browser which passes through server configuration and finally prepares for markup in browser. Also note that phpmyadmin caches the queries and this may cause faster response when you don't expect.

answered Apr 19 '15 at 10:32

Ali Sheikhpour

10,475
5
41
82

Hi Ali, didn't you see that I am using "SQL_NO_CACHE"? phpMyAdmin does not cache queries, MySQL does, and that directive prevents MySQL from caching. Neither is it a browser/html rendering issue, because, as I already mentioned in my question, MySQL slow log reports the time as 20 minutes. That is the actual query time, before results are sent anywhere. – Buttle Butkus Apr 30 '15 at 19:21

score -1 · Answer 3 · edited Jul 12 '18 at 08:04

-1

The reported time is not incorrect, but likely to be misunderstood. phpMyAdmin silently executes another query with "SQL_CALC_FOUND_ROWS"; This is what you see in your MySQL slow log: scanning all rows takes 20min, but the result is only 1 row: the result count.

---> This is quite correct

edited Jul 12 '18 at 08:04

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Jul 12 '18 at 07:41

Subrata Poddar

19
3

You should have commented on the other answer. You haven’t added anything that justifies posting a new answer. – Buttle Butkus Jul 12 '18 at 08:04

phpMyAdmin reports incorrect query time

3 Answers3