Queries, in the form of SELECT
statements, perform all the lookup operations in the database.
Tuning these statements is a top priority, whether to achieve
sub-second response times for dynamic web pages, or to chop
hours off the time to generate huge overnight reports.
Besides SELECT statements, the tuning
techniques for queries also apply to constructs such as
CREATE TABLE...AS SELECT, INSERT
INTO...SELECT, and WHERE clauses in
DELETE statements. Those
statements have additional performance considerations because
they combine write operations with the read-oriented query
operations.
The main considerations for optimizing queries are:
Set up indexes on columns used in the
WHEREclause, to speed up evaluation, filtering, and the final retrieval of results. To avoid wasted disk space, construct a small set of indexes that speed up many related queries used in your application.Minimize the number of table scans in your queries, particularly for big tables.
Keep table statistics up to date, so the optimizer has the information needed to construct an efficient execution plan.
Learn the tuning techniques, indexing techniques, and configuration parameters that are specific to the storage engine for each table. Both
InnoDBandInnoDBhave a set of guidelines to enable and sustain high performance in queries.Isolate and tune any part of the query, such as a function call, that takes excessive time. Remember that such a function might get called millions of times for a big query or insert operation.
Avoid transforming the query in ways that make it hard to understand, especially if the optimizer does some of the same transformations automatically.
If a performance issue is not easily solved by one of the basic guidelines, investigate the internal details of the specific query by reading the
EXPLAINplan and adjusting your indexes,WHEREclauses, join clauses, and so on. (When you reach a certain level of expertise, reading theEXPLAINplan might be your first step for every query.)Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of the
InnoDBbuffer pool,MyISAMkey cache, and the MySQL query cache, repeated queries run faster because the results are retrieved from memory the second and subsequent times.Even for a query that runs fast using the cache memory areas, you might still optimize further so that they require less cache memory, making your application more scalable. Scalability means that your application can handle more simultaneous users, larger requests, and so on without experiencing a big drop in performance.
Deal with locking issues, where the speed of your query might be affected by other sessions accessing the tables at the same time.