SHOW TABLES or INFORMATION_SCHEMA

Question

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

I learned 2 ways to do this.

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 mentioned above that might work better?

2). Is there a performance difference when executing the above two methods?

3). Which of these two methods is best done?

Answer 1

If possible, I would use information_schema . This is the ANSI standard , however access to the proprietary functions of MySql can sometimes use the SHOW * functions. Therefore, I think it depends on your specific situation.

Answer 2

Of course, information_schema . tables preferable to the SHOW TABLES statement (which was used in older versions of MySQL <5.0). This gives more useful information, it is a standard system diagram (you can find a similar diagram in other databases, for example SQL Server). You can use the standard SELECT statement to extract information from this schema, I mean that you can use WHERE, GROUP BY, ORDER BY and other sentences and functions. But sometimes in large databases, the performance of information_schema can be poor.

Take a look at the information_schema performance article: INFORMATION_SCHEMA Optimization .