MySQL Forums
Forum List  »  Performance

"SHOW TABLES LIKE" slow
Posted by: Qiao Zhang
Date: December 13, 2022 10:46AM

We have a wordpress database with 494K tables.
After we upgraded database to MySQL 8, we encountered a performance issue.
"SHOW TABLES LIKE" interally query from information_schema.tables, and using the wrong index.
It should use the index on the TABLE_NAME, but actually it goes to TABLE_SCHEMA first.

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.30 |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT TABLE_NAME AS name
-> FROM information_schema.tables
-> WHERE TABLE_NAME LIKE 'wp_28764_smartcrawl_redirects'
-> and TABLE_SCHEMA = 'wpress';
+-------------------------------+
| name |
+-------------------------------+
| wp_28764_smartcrawl_redirects |
+-------------------------------+
1 row in set (8.09 sec) <-- slow

mysql> SELECT TABLE_NAME AS name
-> FROM information_schema.tables
-> WHERE TABLE_NAME LIKE 'wp_28764_smartcrawl_redirects';
+-------------------------------+
| name |
+-------------------------------+
| wp_28764_smartcrawl_redirects |
+-------------------------------+
1 row in set (0.23 sec) <-- much faster without TABLE_SCHEMA

mysql> show tables;
......
| wp_wfStatus |
| wp_wfThrottleLog |
| wp_wfVulnScanners |
| wp_wiki_subscriptions |
+---------------------------------------------------+
494216 rows in set (8.64 sec) <-- almost the same elapse time of select from information_schema.tables

mysql> SELECT TABLE_NAME AS name
-> FROM information_schema.tables
-> WHERE TABLE_NAME LIKE 'wp_28764_smartcrawl_redirects'
-> and concat(TABLE_SCHEMA,'') = 'wpress';
+-------------------------------+
| name |
+-------------------------------+
| wp_28764_smartcrawl_redirects |
+-------------------------------+
1 row in set (0.21 sec) <-- disable index on TABLE_SCHEMA, it is fast.

mysql> explain
-> SELECT TABLE_NAME AS name
-> FROM information_schema.tables
-> WHERE TABLE_NAME LIKE 'wp_28764_smartcrawl_redirects'
-> and TABLE_SCHEMA = 'wpress';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+-------+----------+-------------+
| 1 | SIMPLE | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | SIMPLE | sch | NULL | eq_ref | PRIMARY,catalog_id | catalog_id | 202 | mysql.cat.id,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | tbl | NULL | ref | schema_id | schema_id | 8 | mysql.sch.id | 56856 | 11.11 | Using where |
| 1 | SIMPLE | col | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.collation_id | 1 | 100.00 | Using index |
| 1 | SIMPLE | ts | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.tablespace_id | 1 | 100.00 | Using index |
| 1 | SIMPLE | stat | NULL | eq_ref | PRIMARY | PRIMARY | 388 | const,mysql.tbl.name | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+-------+----------+-------------+
6 rows in set, 1 warning (0.00 sec)

Any suggestion to fix this issue?
Is there anyting in MySQL 8 like Oracle SQL Profile to give hints to SQL?

Options: ReplyQuote


Subject
Views
Written By
Posted
"SHOW TABLES LIKE" slow
2850
December 13, 2022 10:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.