Understanding the performance statistics from MySQL’s SHOW STATUS command
"SHOW STATUS;" in MySQL returns a large amount of technical data you can use to figure out memory usage, cache performance and resource distribution. Here’s how to read these stats.
Open_tables, Opened_tables: Open_tables shows how many tables are currently open, while Opened_tables shows how many tables have been opened since the MySql server was last restarted. These values give a good picture of how well the table cache is sized. A high Opened_tables value means the cache should be bigger.
Slow_queries shows queries that took longer than expected to run. This value is never zero and depends on how loaded the server is. A high count of slow queries can point to the server being overloaded and struggling to keep up.
Select_scan shows the number of joins, or join queries, that required a full scan of the first table listed in the join. Since a full table scan is resource-heavy, a high value here means you need to optimize the mysql queries in your application code.
Select_full_join shows the number of table joins, or join queries, done without using indexes. Using indexes makes finding data in tables significantly faster. Indexes are recommended for tables where fetching data noticeably affects application performance.
A high value in this field means MySql isn’t using indexes, so it takes longer to build query results. You can fix this, or at least minimize it, by indexing the important fields used in join queries.
Qcache_hits shows the number of hits against cached objects. A high value here means the cache is working well. If it’s low, you can bump up the query-cache-size value in my.cf