MySQL Bugs: #109441: "show tables like" slow without '%' when too many tables

Bug #109441	"show tables like" slow without '%' when too many tables
Submitted:	20 Dec 2022 21:14	Modified:	21 Dec 2022 18:01
Reporter:	Qiao Zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8	OS:	Red Hat (7)
Assigned to:		CPU Architecture:	x86

Description:
Our wordpress database has over 494,216 tables.
The code uses "show tables like '<table_name>'" to check whether tables exist.

It took 2 seconds to return
mysql>  SHOW TABLES LIKE 'wp_smartcrawl_redirects';
+--------------------------------------------+
| Tables_in_wpress (wp_smartcrawl_redirects) |
+--------------------------------------------+
| wp_smartcrawl_redirects                    |
+--------------------------------------------+
1 row in set (1.63 sec)

Without issue using "where"
mysql>  SHOW TABLES where Tables_in_wpress= 'wp_smartcrawl_redirects';
+-------------------------+
| Tables_in_wpress        |
+-------------------------+
| wp_smartcrawl_redirects |
+-------------------------+
1 row in set (0.00 sec)

if searching from information_schema.tables,
it is slow with TABLE_SCHEMA
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 (1.60 sec)

But if I can disable using index on TABLE_SCHEMA
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.19 sec)

How to repeat:
create a database over 400,000 tables

Suggested fix:
I think it is a CBO issue.
without using index on the TABLE_SCHEMA, doing full scan on tbl is faster.

Hi Mr. Zhang,

Thank you for your bug report.

However, we do not think that it is a bug.

Simply, it is a border-line difference in the optimiser part of our server, which depends on many other factors like other queries running and similar. The choice in the optimiser search planning does not work 100 % precisely, so there are a few border-line cases, like the one that you describe ....

It is also documented in our Reference Manual.

Not a bug.

Thanks for your quick response. Do you have any suggestions to fix this issue? Since it is not a SQL and the objects are internal, I couldn't use hint to adjust the execution plan.

Yes, of course .....

In your SELECT statement you can use IGNORE INDEX to make it go faster.

This is explained in our Reference Manual that you can find on dev.mysql.com.

but IGNORE INDEX doesn't work for "show tables like" which is our problem.