SHOW TABLES or INFORMATION_SCHEMA

Question

I need to get list of all tables on server in all databases.

I found out 2 ways for doing that.

1). Execute SHOW FULL TABLES from <each database name> WHERE table_type = 'BASE TABLE';

2). Execute SELECT table_name, table_schema FROM information_schema.tables WHERE TABLE_TYPE = "BASE TABLE";

Questions:

1). Is there any other method then mentioned above that can perform better?

2). Is there any performance difference in executing above two methods?

3). Which of the above two methods is better to execute?

Answer 1

Of course information_schema.tables is more preferable than SHOW TABLES statement (which was used in old MySQL versions < 5.0). It gives more useful information, it is standard system schema (you can find similar schema in other databases, e.g. SQL Server). You can use standard SELECT statement to retrieve information from this schema, I mean you can use WHERE, GROUP BY, ORDER BY and other clauses and functions. But sometimes, on big databases, the performance of information_schema may be bad.

Have a look at the article about the information_schema performance: INFORMATION_SCHEMA Optimization.

Answer 2

Where possible, I would use information_schema. It is an ANSI standard, however, access to proprietary features of MySql may require use of the SHOW* functions on occasion. So I guess it depends on your particular situation.