+------------+------------+--------+ 17 rows in set (0.01 sec) 

Explanation: The statement requests a list of all the tables in database db5 , showing just three pieces of information: the name of the table, its type, and its storage engine. 

## Character Set Considerations 

The definition for character columns (for example, TABLES.TABLE_NAME ) is generally VARCHAR( N ) CHARACTER SET utf8  where N  is at least 64. MySQL uses the default collation for this character set ( utf8_general_ci ) for all searches, sorts, comparisons, and other string operations on such columns. 

Because some MySQL objects are represented as files, searches in INFORMATION_SCHEMA  string columns can be affected by file system case sensitivity. For more information, see Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches” . 

## INFORMATION_SCHEMA as Alternative to SHOW Statements 

The SELECT ... FROM INFORMATION_SCHEMA  statement is intended as a more consistent way to provide access to the information provided by the various SHOW  statements that MySQL supports ( SHOW DATABASES , SHOW TABLES , and so forth). Using SELECT  has these advantages, compared to SHOW : 

•It conforms to Codd’s rules, because all access is done on tables. 

•You can use the familiar syntax of the SELECT  statement, and only need to learn some table and column names. 

•The implementor need not worry about adding keywords. 

•You can filter, sort, concatenate, and transform the results from INFORMATION_SCHEMA  queries into whatever format your application needs, such as a data structure or a text representation to parse. 

•This technique is more interoperable with other database systems. For example, Oracle Database users are familiar with querying tables in the Oracle data dictionary. 

Because SHOW  is familiar and widely used, the SHOW  statements remain as an alternative. In fact, along with the implementation of INFORMATION_SCHEMA , there are enhancements to SHOW  as described in Section 26.8, “Extensions to SHOW Statements” . 

## INFORMATION_SCHEMA and Privileges 

For most INFORMATION_SCHEMA  tables, each MySQL user has the right to access them, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION  column in the INFORMATION_SCHEMA ROUTINES table), users who have insufficient privileges see NULL . Some tables have different privilege requirements; for these, the requirements are mentioned in the applicable table descriptions. For example, InnoDB  tables (tables with names that begin with INNODB_ ) require the PROCESS  privilege. 

The same privileges apply to selecting information from INFORMATION_SCHEMA  and viewing the same information through SHOW  statements. In either case, you must have some privilege on an object to see information about it. 

## Performance Considerations 

INFORMATION_SCHEMA  queries that search for information from more than one database might take a long time and impact performance. To check the efficiency of a query, you can use EXPLAIN . For information about using EXPLAIN  output to tune INFORMATION_SCHEMA  queries, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries” . 

