Beyond the basic SHOW STATUS statistics I described
earlier, the command also offers more technical data which can be used to
assess things like memory usage, cache performance, and resource allocation.
Here are some of the important variables in this category.
This statistic shows you the number of tables currently
open, while the Opened_tables
variables shows the number of tables opened since the server was last
restarted. These values provide a good indication of whether your table cache
is of the right size; a high value for Opened_tables
indicates that your cache should be larger.
This statistic indicates the number of queries that have
taken longer than a predefined per-query time limit. A large value here
indicates that the server is not able to process queries at the speed it should
and is a cause for concern (although you should make this determination after
checking the slow query log, which contains the actual query strings, to see if
it’s the queries themselves that are unusually long or complex).
This statistic shows the number of joins which required a
full scan of the first table named in the join. Since a full table scan is
time- and resource-intensive, a high value here means that your queries are
operating inefficiently and perhaps require further optimization.
This statistic reveals the total number of joins performed
without making use of indexes. An index speeds up table searches and is
advisable on all fields that are queried frequently. Therefore, a high value
here means that MySQL is not using indexes and is therefore taking longer to
build a result set. The problem can be corrected (or at least mitigated) by
indexing important fields of the join.
This statistic shows the number of accesses to the new MySQL
query cache. The query cache stores the results of frequently-used queries so
as to speed up response time; a high value here means that MySQL is working
efficiently by using the cache instead of rebuilding result sets every time the
same query is fired.