The first places to look to improve performance is queries, particularly the ones that run often. Big gains can be achieved by analyzing a query and rewriting it more efficiently.
You can use MySQL’s slow query log to get an idea of which queries might be fine-tuned, and then try applying some of the techniques in the following sections to improve their performance.
As you know, tables with fields that are accessed frequently can be ordered by creating an index. An index points to the place on a database where specific data is located, and creating an index on a field sorts the information in that field. When the server needs to access that information to execute a query, it knows where to look because the index points to the relevant location.
Indexing is even more important on multitable queries. If it takes a while to do a full table scan on one table, imagine how much longer it would take if you have several tables to check. If optimization of your queries is a goal, the first thing to do is to try implementing an index.
Deciding which fields should be indexed involves several considerations. If you have a field involved in searching, grouping, or sorting, indexing it will likely result in a performance gain. These include fields that are part of join operations or fields that appear with clauses such as WHERE, GROUP BY, or ORDER BY.
Certain administrative counters can help you monitor your indexes or come up with candidates for adding an index. Both the SHOW STATUS or mysqladmin extendedstatus commands display values to consider in terms of indexes.
- If your indexes are working, the value of Handler_read_key should be high. This value represents the number of times a record was read by an index value. A low value indicates that not much performance improvement has been achieved by the added indexing because the index isn’t being used frequently.
- A high value for Handler_read_rnd_next means your queries are running inefficiently and indexing should be considered as a remedy. This value indicates the number of requests to read the next row in sequence. This occurs when a table is scanned sequentially from the first record to the last to execute the query. For frequent queries, this is a wasteful use of resources. An associated index points directly to the record(s), so this full table scan doesn’t need to occur. Poorly functioning indexes could also result in a high number here.
To view these counters, run a command like:
SHOW STATUS LIKE 'handler_read%';
TIP — If your SELECT statements frequently end up sorting results by a particular field, use the ALTER TABLE statement with an ORDER BY clause to re-sort the contents of the table by that field. Your SELECT statements will then no longer need an ORDER BY clause, resulting in faster and more efficient reads.
Once you’ve got your tables loaded with data and indexed the way you want them, you should run the ANALYZE TABLE command on them. This command analyzes the data in the table and creates table statistics on the average number of rows that share the same value. This information is used by the MySQL optimizer when deciding which index to use in table joins.
ANALYZE TABLE tablename1, tablename2;
When you run a SELECT query, MySQL “remembers” both the query and the results it returns. This is accomplished by storing the result set in a special cache (called the query cache) each time a SELECT query is executed. Then, the next time you ask the server for the same query, MySQL will retrieve the results from the cache instead of running the query again. As you can imagine, this speeds up the process considerably.
Although enabled by default, you must always verify that query caching is turned on, which can be done by checking the server variables.
SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+
- The first variable, have_query_cache, indicates the server was configured for query caching when it was installed (the default).
- The query_cache_size variable indicates the amount of memory allotted for the cache in bytes. If this value is 0, query caching will be off.
- The values for the query_cache_type variable range from 0 to 2. A value of 0 or OFF indicates that query caching is turned off. ON or 1 means that query caching is turned on, with the exception of SELECT statements using the SQL_NO_CACHE option. DEMAND or 2 provides query caching on demand for SELECT statements running with the SQL_CACHE option.
- The query_cache_limit variable specifies the maximum result set size that should be cached. Result sets larger than this value will not be cached.
CAUTION — Once a table is changed, the cached queries that use this table become invalid and are removed from the cache. This prevents a query from returning inaccurate data from the old table. While this makes query caching much more useful, a constantly changing table won’t benefit from caching. In this situation, you might want to consider eliminating query caching. This can be done by adding the SQL_NO_CACHE option, as previously shown, to a SELECT statement.
Attaching the EXPLAIN keyword to the beginning of a SELECT query tells MySQL to return a chart describing how this query will be processed. This information comes in handy to see which tables should be indexed to speed up performance and to analyze where the bottlenecks are.
The result of EXPLAIN SELECT is a table listing all the SELECTs in the query, together with how MySQL plans to process them.
- The id field indicates the position of the SELECT within the complete query, while the table field holds the name of the table being queried.
- The select_type field indicates the type of query: a simple query without subqueries, a UNION, a subquery, an outer query, a subquery within an outer query, or a subquery in a FROM clause.
- The type field indicates how the join will be performed. A number of values are possible here, ranging from const (the best kind of join, since it means the table contains a single matching record only) to all (the worst kind, because it means that MySQL has to scan every single record to find a match to records in the other joined tables).
- The possible_keys field indicates the indexes available for MySQL to use in order to speed up the search.
- The key field indicates the key it will actually use, with the key length displayed in the key_len field.
- The rows field indicates the number of rows MySQL needs to examine in the corresponding table to successfully execute the query. To obtain the total number of rows MySQL must scan to process the complete query, multiply the rows value for each table together.
- The Extra field contains additional information on how MySQL will process the query—say, by using the WHERE clause, by using an index, with a temporary table, and so on.