Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
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.
