Mariadb – Why do information_schema queries scan all databases

information-schemamariadb

I'm building a PHP application that looks up the last modified date of all databases using information_schema. A simplified version is like this:

$sql = "SELECT update_time FROM information_schema.tables;

I have about 300 databases and this loop takes about 3.1 seconds, so I want to speed this up. I noticed with Explain that any query on information_schema scans all databases, so I'm guessing this is why it's slow. Is there any way to fix this? I'm using the latest MariaDB 10.3.14. 

MariaDB [(none)]> EXPLAIN SELECT update_time FROM information_schema.tables\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tables
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Open_full_table; Scanned all databases
1 row in set (0.000 sec)

EDIT: I tried MySQL 8.0 and now the loop takes 0.03 seconds. Apparently this is a bug, but is there any fix for MariaDB? https://bugs.mysql.com/bug.php?id=81347

Best Answer

The original metadata storage for MySQL was not ideal. It didn't allow transactional DDLs, it had reliability issues, and it was slow and buggy. One of the issues that created was that querying information_schema was slow.

MySQL 8.0, with some foundational work being done in 5.6 and 5.7, finally got rid of the old system (.frms), and that allowed migrating it to a native system, and even being able to create indexes for metadata tables, speeding it up considerably.

MariaDB still uses the old system, but there are tasks filed to (potentially) migrate to a new system, similar to that of MySQL 8: https://jira.mariadb.org/browse/MDEV-11655 (and related tasks). Subscribe that task on MariaDB tracker or consider sponsoring the work. Obviously I don't know, but based on my interactions on the MariaDB JIRA, they will probably want to fix that larger task first.

Related Question