information_schema.DDL_JOBS improvements

[dveeden]

Enhancement

The START_TIME, END_TIME and CREATE_TIME don't show the microsecond level information.

It looks like the microsecond information might be used in some cases.

[dveeden]

mysql> SELECT JOB_ID,START_TIME,END_TIME,TIMEDIFF(END_TIME,START_TIME),TIMESTAMPDIFF(MICROSECOND,START_TIME,END_TIME),END_TIME=START_TIME,CONVERT(END_TIME USING utf8mb4) = CONVERT(START_TIME USING utf8mb4) FROM information_schema.DDL_JOBS ORDER BY TIMEDIFF(END_TIME,START_TIME) DESC LIMIT 20;
+--------+---------------------+---------------------+-------------------------------+------------------------------------------------+---------------------+---------------------------------------------------------------------+
| JOB_ID | START_TIME          | END_TIME            | TIMEDIFF(END_TIME,START_TIME) | TIMESTAMPDIFF(MICROSECOND,START_TIME,END_TIME) | END_TIME=START_TIME | CONVERT(END_TIME USING utf8mb4) = CONVERT(START_TIME USING utf8mb4) |
+--------+---------------------+---------------------+-------------------------------+------------------------------------------------+---------------------+---------------------------------------------------------------------+
|  10106 | 2024-04-26 11:20:02 | 2024-04-26 11:20:17 | 00:00:15                      |                                       15102000 |                   0 |                                                                   0 |
|  10108 | 2024-04-26 11:46:50 | 2024-04-26 11:47:05 | 00:00:15                      |                                       15069000 |                   0 |                                                                   0 |
|  10107 | 2024-04-26 11:45:41 | 2024-04-26 11:45:56 | 00:00:14                      |                                       14780000 |                   0 |                                                                   0 |
|   7455 | 2024-04-26 11:13:17 | 2024-04-26 11:13:17 | 00:00:00                      |                                          21000 |                   0 |                                                                   1 |
|   5105 | 2024-04-26 11:12:58 | 2024-04-26 11:12:58 | 00:00:00                      |                                          21000 |                   0 |                                                                   1 |
|   6653 | 2024-04-26 11:13:10 | 2024-04-26 11:13:10 | 00:00:00                      |                                          20000 |                   0 |                                                                   1 |
|   8723 | 2024-04-26 11:13:27 | 2024-04-26 11:13:27 | 00:00:00                      |                                          16000 |                   0 |                                                                   1 |
|   5877 | 2024-04-26 11:13:04 | 2024-04-26 11:13:04 | 00:00:00                      |                                          14000 |                   0 |                                                                   1 |
|   9125 | 2024-04-26 11:13:29 | 2024-04-26 11:13:29 | 00:00:00                      |                                          14000 |                   0 |                                                                   1 |
|   9959 | 2024-04-26 11:13:35 | 2024-04-26 11:13:35 | 00:00:00                      |                                          14000 |                   0 |                                                                   1 |
|   6767 | 2024-04-26 11:13:11 | 2024-04-26 11:13:11 | 00:00:00                      |                                          14000 |                   0 |                                                                   1 |
|   2851 | 2024-04-26 11:12:34 | 2024-04-26 11:12:34 | 00:00:00                      |                                          14000 |                   0 |                                                                   1 |
|   7679 | 2024-04-26 11:13:19 | 2024-04-26 11:13:19 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   3953 | 2024-04-26 11:12:41 | 2024-04-26 11:12:41 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   6267 | 2024-04-26 11:13:07 | 2024-04-26 11:13:07 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   3579 | 2024-04-26 11:12:38 | 2024-04-26 11:12:38 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   2455 | 2024-04-26 11:12:32 | 2024-04-26 11:12:32 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   6149 | 2024-04-26 11:13:06 | 2024-04-26 11:13:06 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   5491 | 2024-04-26 11:13:01 | 2024-04-26 11:13:01 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
|   2863 | 2024-04-26 11:12:34 | 2024-04-26 11:12:34 | 00:00:00                      |                                          13000 |                   0 |                                                                   1 |
+--------+---------------------+---------------------+-------------------------------+------------------------------------------------+---------------------+---------------------------------------------------------------------+
20 rows in set (0.12 sec)

So it looks like TIMEDIFF() is able to get microsecond info from these columns. It also looks like 2024-04-26 11:13:17 and 2024-04-26 11:13:17 are not considered equal, probably because there are differences in microsecond level.

[dveeden]

This is one of the items where microsecond make sense:

mysql> SELECT JOB_TYPE,AVG(TIMEDIFF(END_TIME,START_TIME)) 'avg time', MIN(TIMEDIFF(END_TIME,START_TIME)) 'min time', MAX(TIMEDIFF(END_TIME,START_TIME)) 'max time',COUNT(*),AVG(TIMESTAMPDIFF(MICROSECOND,START_TIME,END_TIME)) 'avg µs', MIN(TIMESTAMPDIFF(MICROSECOND,START_TIME,END_TIME)) 'min µs', MAX(TIMESTAMPDIFF(MICROSECOND,START_TIME,END_TIME)) 'max µs' FROM information_schema.DDL_JOBS GROUP BY JOB_TYPE ORDER BY 1;
+---------------------------+----------+----------+----------+----------+---------------+----------+----------+
| JOB_TYPE                  | avg time | min time | max time | COUNT(*) | avg µs        | min µs   | max µs   |
+---------------------------+----------+----------+----------+----------+---------------+----------+----------+
| add index /* txn-merge */ |  14.6667 | 00:00:14 | 00:00:15 |        3 | 14983666.6667 | 14780000 | 15102000 |
| create schema             |   0.0000 | 00:00:00 | 00:00:00 |        2 |     6500.0000 |     6000 |     7000 |
| create table              |   0.0000 | 00:00:00 | 00:00:00 |     5057 |     6519.8734 |     4000 |    21000 |
| create view               |   0.0000 | 00:00:00 | 00:00:00 |        2 |     5500.0000 |     5000 |     6000 |
| drop table                |   0.0000 | 00:00:00 | 00:00:00 |        9 |    52888.8889 |    16000 |   119000 |
+---------------------------+----------+----------+----------+----------+---------------+----------+----------+
5 rows in set (0.18 sec)