Optimize LIMIT the number of rows to be SELECT in SQL-CodePudding

Consider a table Test having 1000 rows

Test Table

id   name desc
1    Adi  test1
2    Sam  test2
3    Kal  test3
.
.
1000 Jil test1000

If i need to fetch, say suppose 100 rows(i.e. a small subset) only, then I am using LIMIT clause in my query

SELECT * FROM test LIMIT 100;

This query first fetches 1000 rows and then returns 100 out of it.

Can this be optimised, such that the DB engine queries only 100 rows and returns them

(instead of fetching all 1000 rows first and then returning 100)

Reason for above supposition is that the order of processing will be

FROM
WHERE
SELECT
ORDER BY
LIMIT

CodePudding user response：

You can combine LIMIT ROW COUNT with an ORDER BY, This causes MySQL to stop sorting as soon as it has found the first ROW COUNT rows of the sorted result. Hope this helps, If you need any clarification just drop a comment.

CodePudding user response：

The query you wrote will fetch only 100 rows, not 1000. But, if you change that query in any way, my statement may be wrong.

GROUP BY and ORDER BY are likely to incur a sort, which is arguably even slower than a full table scan. And that sort must be done before seeing the LIMIT.

Well, not always...

 SELECT ... FROM t ORDER BY x LIMIT 100;

together with INDEX(x) -- This may use the index and fetch only 100 rows from the index. BUT... then it has to reach into the data 100 times to find the other columns that you ask for. UNLESS you only ask for x.

Etc, etc.

And here's another wrinkle. A lot of questions on this forum are "Why isn't MySQL using my index?" Back to your query. If there are "only" 1000 rows in your table, my example with the ORDER BY x won't use the index because it is faster to simply read through the table, tossing 90% of the rows. On the other hand, if there were 9999 rows, then it would use the index. (The transition is somewhere around 20%, but it that is imprecise.)

Confused? Fine. Let's discuss one query at a time. I can [probably] discuss the what and why of each one you throw at me. Be sure to include SHOW CREATE TABLE, the full query, and EXPLAIN SELECT... That way, I can explain what EXPLAIN tells you (or does not).

Did you know that having both a GROUP BY and ORDER BY may cause the use of two sorts? EXPLAIN won't point that out. And sometimes there is a simple trick to get rid of one of the sorts.

There are a lot of tricks up MySQL's sleeve.