Could someone help me with this issue?
When I execute the command SHOW PROFILES, I got a time duration which is different from the command SHOW PROFILE (without "S"), in this case, which one do I have to consider?
e.g: For SHOW PROFILES
query | DURATION
53 0.08104775
for INFORMATION_SCHEMA.PROFILING (SHOW PROFILE)
SET @query_id := 53;
SELECT SUM(Total_R) FROM (
SELECT STATE, SUM(DURATION) AS Total_R,
ROUND(
100 * SUM(DURATION) /
(SELECT SUM(DURATION)
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
), 2) AS Pct_R,
COUNT(*) AS Calls,
SUM(DURATION) / COUNT(*) AS "R/Call"
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = @query_id
GROUP BY STATE
ORDER BY Total_R DESC ) AS X ;
query | TOTAL DURATION
53 0.000430
I'm getting confused about which time is the correct one.
A simple SUM(...) query seems to deliver pretty much the same value as SHOW PROFILES "duration" column:
(tested on MySQL 5.1 and Percona 8.0)
I yet don't understand the difference between the profiling duration values (0.000107 and 0.000108) and the time reported right underneath the query result ("1 row in set (0.02 sec)").