Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Any query to information_schema is extremely slow because it scans all databases. For example, this query takes over 3 seconds. This bug has been fixed in MySQL 8.0, so I tested in that version using the same data and this same query takes 0.03 seconds.
https://bugs.mysql.com/bug.php?id=81347
SELECT `update_time` FROM information_schema.tables;
|
...
|
5068 rows in set (3.289 sec)
|
|
|
EXPLAIN SELECT update_time FROM information_schema.tables;
|
+------+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
|
| 1 | SIMPLE | tables | ALL | NULL | NULL | NULL | NULL | NULL | Open_full_table; Scanned all databases |
|
+------+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+
|
1 row in set (0.002 sec)
|
|
Attachments
Issue Links
- relates to
-
MDEV-11655 Transactional data dictionary
-
- Open
-
When it comes to InnoDB tables, there is no persistent update_time (or create_time, for that matter). That particular query might be executed without opening all tables internally, by simply returning 0 for those tables that do not exist in the cache.
MySQL 8.0 replaced the .frm file based data dictionary with the Global Data Dictionary, which stores the data dictionary information in InnoDB tables. They made most INFORMATION_SCHEMA tables views of existing tables. Those columns that are not stored in the dictionary would still be computed.
I believe that also MySQL 8.0 would open all tables for this query, to ask the storage engine to fill in the update_time, but that would be somewhat faster, because instead of reading a large number of .frm files, it would read the global data dictionary tables from the InnoDB buffer pool.
A long time ago, I wrote in MDEV-11655 some thoughts on designing and implementing a better data dictionary.