In general, when you want to make a slow SELECT ...
WHERE query faster, the first thing to check is
whether you can add an index. 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 7.8.1, “Optimizing Queries with EXPLAIN”, and
Section 7.3.1, “How MySQL Uses Indexes”.
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;
© 2012, Oracle Corporation and/or its affiliates
User Comments
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;
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 ...
Add your own comment.