As of MySQL 5.1.23, the implementation of
INFORMATION_SCHEMA
is such that certain types
of queries for INFORMATION_SCHEMA
tables can
be optimized to execute more quickly. This section provides
guidelines on writing queries that take advantage of these
optimizations. In general, the strategies outlined here minimize
the need for the server to access the file system to obtain the
information that makes up the contents of
INFORMATION_SCHEMA
tables. By writing queries
that enable the server to avoid directory scans or opening table
files, you will obtain better performance.
1) Try to use constant lookup values for
database and table names in the WHERE
clause
You can take advantage of this principle as follows:
To look up databases or tables, use expressions that evaluate to a constant, such as literal values, functions that return a constant, or scalar subqueries.
Avoid queries that use a nonconstant database name lookup value (or no lookup value) because they require a scan of the data directory to find matching database directory names.
Within a database, avoid queries that use a nonconstant table name lookup value (or no lookup value) because they require a scan of the database directory to find matching table files.
This principle applies to the
INFORMATION_SCHEMA
tables shown in the
following table, which shows the columns for which a constant
lookup value enables the server to avoid a directory scan. For
example, if you are selecting from
TABLES
, using a constant lookup
value for TABLE_SCHEMA
in the
WHERE
clause enables a data directory scan to
be avoided.
Table | Column to specify to avoid data directory scan | Column to specify to avoid database directory scan |
COLUMNS |
TABLE_SCHEMA |
TABLE_NAME |
KEY_COLUMN_USAGE |
TABLE_SCHEMA |
TABLE_NAME |
PARTITIONS |
TABLE_SCHEMA |
TABLE_NAME |
REFERENTIAL_CONSTRAINTS |
CONSTRAINT_SCHEMA |
TABLE_NAME |
STATISTICS |
TABLE_SCHEMA |
TABLE_NAME |
TABLES |
TABLE_SCHEMA |
TABLE_NAME |
TABLE_CONSTRAINTS |
TABLE_SCHEMA |
TABLE_NAME |
TRIGGERS |
EVENT_OBJECT_SCHEMA |
EVENT_OBJECT_TABLE |
VIEWS |
TABLE_SCHEMA |
TABLE_NAME |
The benefit of a query that is limited to a specific constant database name is that checks need be made only for the named database directory. Example:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';
Use of the literal database name test
enables
the server to check only the test
database
directory, regardless of how many databases there might be. By
contrast, the following query is less efficient because it
requires a scan of the data directory to determine which
database names match the pattern 'test%'
:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'test%';
For a query that is limited to a specific constant table name, checks need be made only for the named table within the corresponding database directory. Example:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
Use of the literal table name t1
enables the
server to check only the files for the t1
table, regardless of how many tables there might be in the
test
database. By contrast, the following
query requires a scan of the test
database
directory to determine which table names match the pattern
't%'
:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';
The following query requires a scan of the database directory to
determine matching database names for the pattern
'test%'
, and for each matching database, it
requires a scan of the database directory to determine matching
table names for the pattern 't%'
:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';
2) Write queries that minimize the number of table files that must be opened
For queries that refer to certain
INFORMATION_SCHEMA
table columns, several
optimizations are available that minimize the number of table
files that must be opened. Example:
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';
In this case, after the server has scanned the database
directory to determine the names of the tables in the database,
those names become available with no further file system
lookups. Thus, TABLE_NAME
requires no files
to be opened. The ENGINE
(storage engine)
value can be determined by opening the table's
.frm
file, without touching other table
files such as the .MYD
or
.MYI
file.
Some values, such as INDEX_LENGTH
for
MyISAM
tables, require opening the
.MYD
or .MYI
file as
well.
The file-opening optimization types are denoted thus:
SKIP_OPEN_TABLE
: Table files do not need
to be opened. The information has already become available
within the query by scanning the database directory.
OPEN_FRM_ONLY
: Only the table's
.frm
file need be opened.
OPEN_TRIGGER_ONLY
: Only the table's
.TRG
file need be opened.
OPEN_FULL_TABLE
: The unoptimized
information lookup. The .frm
,
.MYD
, and .MYI
files must be opened.
The following list indicates how the preceding optimization
types apply to INFORMATION_SCHEMA
table
columns. For tables and columns not named, none of the
optimizations apply.
COLUMNS
:
OPEN_FRM_ONLY
applies to all columns
KEY_COLUMN_USAGE
:
OPEN_FULL_TABLE
applies to all columns
PARTITIONS
:
OPEN_FULL_TABLE
applies to all columns
REFERENTIAL_CONSTRAINTS
:
OPEN_FULL_TABLE
applies to all columns
Column | Optimization type |
TABLE_CATALOG |
OPEN_FRM_ONLY |
TABLE_SCHEMA |
OPEN_FRM_ONLY |
TABLE_NAME |
OPEN_FRM_ONLY |
NON_UNIQUE |
OPEN_FRM_ONLY |
INDEX_SCHEMA |
OPEN_FRM_ONLY |
INDEX_NAME |
OPEN_FRM_ONLY |
SEQ_IN_INDEX |
OPEN_FRM_ONLY |
COLUMN_NAME |
OPEN_FRM_ONLY |
COLLATION |
OPEN_FRM_ONLY |
CARDINALITY |
OPEN_FULL_TABLE |
SUB_PART |
OPEN_FRM_ONLY |
PACKED |
OPEN_FRM_ONLY |
NULLABLE |
OPEN_FRM_ONLY |
INDEX_TYPE |
OPEN_FULL_TABLE |
COMMENT |
OPEN_FRM_ONLY |
Column | Optimization type |
TABLE_CATALOG |
SKIP_OPEN_TABLE |
TABLE_SCHEMA |
SKIP_OPEN_TABLE |
TABLE_NAME |
SKIP_OPEN_TABLE |
TABLE_TYPE |
OPEN_FRM_ONLY |
ENGINE |
OPEN_FRM_ONLY |
VERSION |
OPEN_FRM_ONLY |
ROW_FORMAT |
OPEN_FULL_TABLE |
TABLE_ROWS |
OPEN_FULL_TABLE |
AVG_ROW_LENGTH |
OPEN_FULL_TABLE |
DATA_LENGTH |
OPEN_FULL_TABLE |
MAX_DATA_LENGTH |
OPEN_FULL_TABLE |
INDEX_LENGTH |
OPEN_FULL_TABLE |
DATA_FREE |
OPEN_FULL_TABLE |
AUTO_INCREMENT |
OPEN_FULL_TABLE |
CREATE_TIME |
OPEN_FULL_TABLE |
UPDATE_TIME |
OPEN_FULL_TABLE |
CHECK_TIME |
OPEN_FULL_TABLE |
TABLE_COLLATION |
OPEN_FRM_ONLY |
CHECKSUM |
OPEN_FULL_TABLE |
CREATE_OPTIONS |
OPEN_FRM_ONLY |
TABLE_COMMENT |
OPEN_FRM_ONLY |
TABLE_CONSTRAINTS
:
OPEN_FULL_TABLE
applies to all columns
TRIGGERS
:
OPEN_FULL_TABLE
applies to all columns
Column | Optimization type |
TABLE_CATALOG |
OPEN_FRM_ONLY |
TABLE_SCHEMA |
OPEN_FRM_ONLY |
TABLE_NAME |
OPEN_FRM_ONLY |
VIEW_DEFINITION |
OPEN_FULL_TABLE |
CHECK_OPTION |
OPEN_FULL_TABLE |
IS_UPDATABLE |
OPEN_FULL_TABLE |
DEFINER |
OPEN_FULL_TABLE |
SECURITY_TYPE |
OPEN_FULL_TABLE |
CHARACTER_SET_CLIENT |
OPEN_FULL_TABLE |
COLLATION_CONNECTION |
OPEN_FULL_TABLE |
3) Use
EXPLAIN
to determine whether the
server can use INFORMATION_SCHEMA
optimizations for a query
The Extra
value in
EXPLAIN
output indicates which,
if any, of the optimizations described earlier the server can
use to evaluate INFORMATION_SCHEMA
queries.
The following examples demonstrate the kinds of information you
can expect to see in the Extra
value.
mysql>EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
->TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: VIEWS type: ALL possible_keys: NULL key: TABLE_SCHEMA,TABLE_NAME key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned 0 databases
Use of constant database and table lookup values enables the
server to avoid directory scans. For references to
VIEWS.TABLE_NAME
, only the
.frm
file need be opened.
mysql> EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: TABLES
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Open_full_table; Scanned all databases
No lookup values are provided (there is no
WHERE
clause), so the server must scan the
data directory and each database directory. For each table thus
identified, the table name and row format are selected.
TABLE_NAME
requires no further table files to
be opened (the SKIP_OPEN_TABLE
optimization
applies). ROW_FORMAT
requires all table files
to be opened (OPEN_FULL_TABLE
applies).
EXPLAIN
reports
OPEN_FULL_TABLE
because it is more expensive
than SKIP_OPEN_TABLE
.
mysql>EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
->WHERE TABLE_SCHEMA = 'test'\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: TABLES type: ALL possible_keys: NULL key: TABLE_SCHEMA key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned 1 database
No table name lookup value is provided, so the server must scan
the test
database directory. For the
TABLE_NAME
and TABLE_TYPE
columns, the SKIP_OPEN_TABLE
and
OPEN_FRM_ONLY
optimizations apply,
respectively. EXPLAIN
reports
OPEN_FRM_ONLY
because it is more expensive.
mysql>EXPLAIN SELECT B.TABLE_NAME
->FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
->WHERE A.TABLE_SCHEMA = 'test'
->AND A.TABLE_NAME = 't1'
->AND B.TABLE_NAME = A.TABLE_NAME\G
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: A type: ALL possible_keys: NULL key: TABLE_SCHEMA,TABLE_NAME key_len: NULL ref: NULL rows: NULL Extra: Using where; Skip_open_table; Scanned 0 databases *************************** 2. row *************************** id: 1 select_type: SIMPLE table: B type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using where; Open_frm_only; Scanned all databases; Using join buffer
For the first EXPLAIN
output row:
Constant database and table lookup values enable the server to
avoid directory scans for TABLES
values.
References to TABLES.TABLE_NAME
require no
further table files.
For the second EXPLAIN
output
row: All COLUMNS
table values are
OPEN_FRM_ONLY
lookups, so
COLUMNS.TABLE_NAME
requires the
.frm
file to be opened.
mysql> EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: COLLATIONS
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
In this case, no optimizations apply because
COLLATIONS
is not one of the
INFORMATION_SCHEMA
tables for which
optimizations are available.
User Comments
Add your own comment.