1

MySQL.

My heavy query crashed our MySQL server.

If I add LIMIT 10 will it make my query more lightweight? (both for indexed and unindexed queries).

Which way LIMIT are processed: before the rest of the work or after?

I feel that LIMIT makes indexed queries more lightweight, but does not do for unindexed queries. Right thought?

  • 1
    Have you tried looking at the docs? dev.mysql.com/doc/refman/5.5/en/limit-optimization.html – zgguy Jun 7 '15 at 12:47
  • @zgguy I've read the docs you suggested. Docs say that it does some additional optimization in some cases. But it is not said explicitly whether it does optimization in other cases (not described in the docs). Should I assume that in the cases not covered by the docs no optimization is done? – porton Jun 7 '15 at 12:57
  • You can check your own queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then some optimizations were performed (query can be executed more efficiently because of limit by clause, or at least the optimizer thinks so). – zgguy Jun 7 '15 at 13:21
  • @zgguy Your comments should be combined and made into an answer. – RolandoMySQLDBA Jun 7 '15 at 20:08
3

By suggestion of @RolandoMySQLDBA, I'm combining my comments into an answer.

So, in many cases, LIMIT clause enables the database server to do less work before delivering the result. MySQL docs explain such situations.

It is best to check queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then some optimizations were performed (query can be executed more efficiently because of limit by clause, or at least the optimizer thinks so).

For example, using the Sakila test database: a table film has a column named title which is indexed. It has 1000 rows. EXPLAIN for query

select * from film order by title

shows that the databases reads and sorts the entire table (this being cheaper than using the index on title and accessing table rows from index entries, since the whole table must be read). On the other hand, EXPLAIN for the same query with LIMIT clause,

select * from film order by title limit 10 

shows that the database now decides to use index on title column; it only needs to read the first 10 index entries and retrieve rows that those entries point to. The rest of the index or table are never accessed. Certainly much cheaper than the first query, if you only need the first 10 rows.

1

It depends.

INDEX(a)
SELECT ... ORDER BY a LIMIT 10
-- will, if there is no `WHERE`, stop after 10 rows are read

INDEX(b,a)
SELECT ... WHERE b=3 ORDER BY a LIMIT 10
-- is likely to use the index for both the WHERE and the ORDER BY, and stop after 10

INDEX(b,a)
SELECT ... WHERE b=3 AND c=4 ORDER BY a LIMIT 10
-- This _may_ use the index, but _may_ have to keep reading
-- until it finds 10 rows that also have `c=4`

SELECT ... ORDER BY a ASC, b DESC LIMIT 10
-- No index will help the LIMIT be consumed.

SELECT ... GROUP BY x  ORDER BY y LIMIT 10
-- logically needs two sorts, so it must gather all the rows -- heavyweight

There are a zillion more cases; show us what might be of interest if you like.

More discussion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.