1

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

1

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.

  • 1
    My impression is that the rewrite of I_S for 8.0 was massive. I am surprised that Oracle made it as solid as it is without having major show-stopping crashes. Bravos to Oracle; good luck to MariaDB in porting so much code. (I have no insight on when, or even whether, MariaDB will undertake the task.) – Rick James Apr 25 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.