MySQL 5.1 Reference Manual  /  ...  /  Speed of SELECT Statements Speed of SELECT Statements

The main considerations for optimizing queries are:

  • To make a slow SELECT ... WHERE query faster, the first thing to check is whether you can add an index. Set up indexes on columns used in the WHERE clause, 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.

    Indexes are especially important for queries that reference different tables, using features such as joins and foreign keys. You can use the EXPLAIN statement to determine which indexes are used for a SELECT. See Section 8.3.1, “How MySQL Uses Indexes” and Section 8.8.1, “Optimizing Queries with EXPLAIN”.

  • Isolate and tune any part of the query, such as a function call, that takes excessive time. Depending on how the query is structured, a function could be called once for every row in the result set, or even once for every row in the table, greatly magnifying any inefficiency.

  • Minimize the number of full table scans in your queries, particularly for big tables.

  • Keep table statistics up to date by using the ANALYZE TABLE statement periodically, 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 InnoDB and MyISAM have sets of guidelines for enabling and sustaining high performance in queries. For details, see Section 8.6.5, “Optimizing InnoDB Queries” and Section 8.5.1, “Optimizing MyISAM Queries”.

  • 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 EXPLAIN plan and adjusting your indexes, WHERE clauses, join clauses, and so on. (When you reach a certain level of expertise, reading the EXPLAIN plan 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 MyISAM key cache, InnoDB buffer pool, 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.

Download this Manual
User Comments
  Posted by Alec Matusis on June 11, 2006
If you need to fetch a small number of rows from a table with 10s of millions of rows, LIMIT offset, n_rows will become increasingly slow as offset increases.

A way around this is to have a unique index that numbers the rows, and base your offset on that index. Example:

SELECT * FROM very_long_table WHERE id > 20000000 LIMIT 20;

is MUCH faster than

SELECT * FROM very_long_table LIMIT 20000000, 20;
  Posted by Stanciu Ionut on February 6, 2007
SELECT * FROM very_long_table WHERE id > 20000000 LIMIT 20;

is MUCH faster than

SELECT * FROM very_long_table LIMIT 20000000, 20;

But it`s not the same thing ...
  Posted by Biswajit Maji on September 24, 2013
these two are not same because it may not have all row entries from 1 to 20000000

  Posted by Awlad Hossain on October 30, 2013
1. SELECT * FROM very_long_table WHERE id > 20000000 LIMIT 20;
2. SELECT * FROM very_long_table LIMIT 20000000, 20;
Indeed the #1 is faster, the #2 is fetching the record for 20000000 + 20 lines from which last 20 records will be shown. And there's a chance that no record will be shown if the case is some records were deleted from the table.

  Posted by Vijay Kumar Sati on March 3, 2015
1. SELECT * FROM very_long_table WHERE id > 20000000 LIMIT 20;

2. SELECT * FROM very_long_table LIMIT 20000000, 20;

Both queries are different and have different outcomes.

1st Query: Will fetch all rows where id > 20000000 and then limit display of records to 20.

2nd Query: Will select all the records from very_long_table(maybe unordered way) and then from the fetched records show the 20 records after the 20000000th record. So it may fetch a record with id = 10000 too as records are unordered.

Solution: "Use ORDER BY id" in both queries to have the same result.

Note: Also for this case we've to assume that column id is primary key and must has values in range [0 - infinite] and no record is deleted from the table(Such a rare case).

So both queries can't be used for same purpose in a generalized manner.
Sign Up Login You must be logged in to post a comment.