phpMyAdmin - Query Execution Time

Question

When I execute a simple statement in phpMyAdmin like

SELECT *
FROM a

where "a" has 500'000 rows, it gives me a time of a few milliseconds on my localhost.

Some complex queries report times that are way longer (as expected), but some queries report also very fast times < 1/100s but the result page in phpMyAdmin takes way longer to display.

So I'm unsure, is the displayed execution time really true and accurate in phpMyAdmin? How is it measured? Does it measure the whole query with all subselects, joins etc?

Thanks!

UPDATE

I thought it'd be a good idea to test from my own PHP-script like:

$start = microtime(true);
$sql = "same statement as in phpMyAdmin";
$db = new PDO('mysql:host=localhost;dbname=mydb', 'root', 'lala');
$statement = $db -> prepare($sql);
$statement -> execute();
echo microtime(true) - $start . ' seconds';

and that takes more than 7 seconds compared to a reported time in phpMyAdmin for the same statement of 0.005s. The query returns 300'000 rows, if I restrict it to 50 with "LIMIT 0,50" it's under 1/100s. Where does that difference come from? I don't iterate over the returned objects or something...

Answer 1

The displayed execution time is how long the query took to run on the server - it's accurate and comes from the MySQL engine itself. Unfortunately, the results have to then be sent over the web to your browser to be displayed, which takes a lot longer.

Answer 2

phpMyAdmin automatically appends a LIMIT clause to your statement, so it has a smaller result set to return thus making it faster.

Even if you need all 300,000 or 500,000 results then you should really use a LIMIT. Multiple smaller queries does not necessarily mean same execution time as a single big query.