0

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 ;

enter image description here

query  | TOTAL DURATION
53       0.000430

I'm getting confused about which time is the correct one.

Doug P
  • 169
  • 1
  • 3
  • 13

1 Answers1

0

A simple SUM(...) query seems to deliver pretty much the same value as SHOW PROFILES "duration" column:

mysql> SET profiling = 1;
mysql> SELECT SQRT(100);
+-----------+
| SQRT(100) |
+-----------+
|        10 |
+-----------+
1 row in set (0.02 sec)

mysql> SHOW PROFILES;
+----------+------------+------------------+
| Query_ID | Duration   | Query            |
+----------+------------+------------------+
|        1 | 0.00010700 | SELECT SQRT(100) |
+----------+------------+------------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT SUM(DURATION) FROM information_schema.PROFILING WHERE QUERY_ID = 1;
+---------------+
| SUM(DURATION) |
+---------------+
|      0.000108 |
+---------------+

(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)").

MaWoe
  • 1