Databases Reference
In-Depth Information
Handler Operations
The handler API is the interface between MySQL and its storage engines. The
Hand
ler_*
variables count handler operations, such as the number of times MySQL asks a
storage engine to read the next row from an index. You can view these variables with:
mysql>
SHOW GLOBAL STATUS LIKE 'Handler_%';
MyISAM Key Buffer
The
Key_*
variables contain metrics and counters about the MyISAM key buffer. You
can view these variables with:
mysql>
SHOW GLOBAL STATUS LIKE 'Key_%';
File Descriptors
If you mainly use the MyISAM storage engine the Open_* variables reveal how often
MySQL opens each table's
.frm, .MYI
, and
.MYD
files. InnoDB keeps all data in its
tablespace files, so if you mainly use InnoDB, these variables aren't accurate. You can
view the
Open_*
variables with:
mysql>
SHOW GLOBAL STATUS LIKE 'Open_%';
Query Cache
You can inspect the query cache by looking at the
Qcache_*
status variables, with:
mysql>
SHOW GLOBAL STATUS LIKE 'Qcache_%';
SELECT Types
The
Select_*
variables are counters for certain types of
SELECT
queries. They can help
you see the ratio of
SELECT
queries that use various query plans. Unfortunately, there
are no such status variables for other kinds of queries, such as
UPDATE
and
REPLACE
;
however, you can look at the
Handler_*
status variables (discussed earlier) for insight
into the relative numbers of non-
SELECT
queries. To see all the
Select_*
variables, use:
mysql>
SHOW GLOBAL STATUS LIKE 'Select_%';
In our judgment, the
Select_*
status variables can be ranked as follows, in order of
ascending cost:
Select_range
The number of joins that scanned an index range on the first table.
Select_scan
The number of joins that scanned the entire first table. There is nothing wrong
with this if every row in the first table should participate in the join; it's only a bad
thing if you don't want all the rows and there is no index to find the ones you want.
