Getting information about databases and tables

You might have already noticed an information_schema database in the list of databases. information_schema is a collection of views that consist of metadata about all the database objects. You can connect to information_schema and explore all the tables. The most widely-used tables are explained in this chapter. You either query the information_schema tables or use the SHOW command, which essentially does the same.

INFORMATION_SCHEMA queries are implemented as views over the data dictionary tables. There are two types of metadata in the INFORMATION_SCHEMA tables:

• Static table metadata: TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, and ENGINE. These statistics will be read directly from the data dictionary.
• Dynamic table metadata: AUTO_INCREMENT, AVG_ROW_LENGTH, and DATA_FREE. Dynamic metadata frequently changes (for example, the AUTO_INCREMENT value will advance after each INSERT). In many cases, the dynamic metadata will also incur some cost to accurately...