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
, andENGINE
. These statistics will be read directly from thedata dictionary
. - Dynamic table metadata:
AUTO_INCREMENT
,AVG_ROW_LENGTH
, andDATA_FREE
. Dynamic metadata frequently changes (for example, theAUTO_INCREMENT
value will advance after eachINSERT
). In many cases, the dynamic metadata will also incur some cost to accurately...