Bug #81865 SHOW FULL TABLES significant slow
Submitted: 15 Jun 2016 12:20 Modified: 2 May 2022 6:48
Reporter: Oli Sennhauser Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6.2x, 5.6.31 OS:Linux
Assigned to: CPU Architecture:Any

[15 Jun 2016 12:20] Oli Sennhauser 
Description:
SHOW FULL TABLES is significantly slower than SHOW TABLES or SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = 'my_schema'

on systems with huge amount of tables (100k and more) and a user defined with

GRANT ALL on `schema_%`.* to user@host;

How to repeat:
Create 500 schema with 200 tables each.
create user as described.
run show full tables;

show profile will show that it hangs in state "Checking Permissions"

Suggested fix:
it should do it the same way as I_S.tables does.
[20 Jun 2016 9:45] MySQL Verification Team 
Hello Oli Sennhauser,

Thank you for the report.

Thanks,
Umesh
[2 May 2022 6:48] Karthik Kamath Koteshwar 
Posted by developer:
 
This is not a bug because SHOW TABLES just lists the tables in a database directory whereas SHOW FULL TABLES has to open the 'FRM's of all the tables in the database to collect additional information: "table_type" which could be one among "BASE TABLE for a table, VIEW for a view, or SYSTEM VIEW for an INFORMATION_SCHEMA table". Hence, it is bound to take more time than that of SHOW TABLES. Closing this as 'not a bug'.