Bug #81865 SHOW FULL TABLES significant slow
Submitted: 15 Jun 2016 12:20 Modified: 20 Jun 2016 9:45
Reporter: Oli Sennhauser Email Updates:
Status: Verified 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] Umesh Shastry
Hello Oli Sennhauser,

Thank you for the report.

Thanks,
Umesh