CODEWITHSUNDEEP
mysqlperformance
Marek Knappe
Marek Knappe

Reputation: 23

Select from information_schema tables very slow

Query for * from information_schema.tables is very slow. Innodb_stats_on_metadata is off, and select table_name from tables is fast, just selecting more fields is very slow (12 minutes!)

mysql> select * from tables limit 1;
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME     | TABLE_TYPE  | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | information_schema | CHARACTER_SETS | SYSTEM VIEW | MEMORY |      10 | Fixed      |       NULL |            384 |           0 |        32869632 |            0 |         0 |           NULL | 2016-12-19 23:55:46 | NULL        | NULL       | utf8_general_ci |     NULL | max_rows=87381 |               |
+---------------+--------------------+----------------+-------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (**12 min 27.02 sec**)

Additional information:

mysql> select count(*) from tables;
+----------+ 
| count(*) |
+----------+
|   194196 |
+----------+
1 row in set (0.57 sec)
mysql> show  global variables like '%innodb%metada%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

Upvotes: 2

Views: 1915

Answers (2)

Rick James
Rick James

Reputation: 142298

Even in 5.7, the information about the tables is scattered in files on disk. Reading 200K files takes a lot of time.

That is one reason why 200K tables is not a good design. Other reasons have to do with caching -- there are practical limits on such.

You will see variations on timings of I_S queries because of caching.

Advice: Re-think your schema design.

8.0 Stores all that info in an InnoDB table, so it will be enormously faster.

Upvotes: 0

Michael - sqlbot
Michael - sqlbot

Reputation: 179104

Selecting more columns means the server has to do more work -- interrogating the storage engines for all of the tables in all of the schemas to obtain what you requested.

The tables in information_schema are not real tables. They are server internals, exposed via an SQL interface, in some cases allowing you to query information the server doesn't store and must calculate or gather because you asked. The server code knows what columns you ask for, and only gathers that information.

LIMIT 1 doesn't help, because information_schema doesn't handle LIMIT as you would expect -- the entire table is rendered in memory before the first row is returned and the rest are discarded.

Upvotes: 1

Related Questions