Mysql – Does using LIMIT improve the performance and is it noticeable

join;MySQLperformance

I want to understand the following.
Assume that I have a complicated query with let's say a join of 5 tables a group by summations and order by.
Letting aside any optimizations to the query itself e.g. indexes etc.
Is there any significant performance benefit using LIMIT? I assume that all the query (and results) must be processed before LIMIT is applied, so using a LIMIT to retrieve a subset of the results, does this offer any significant/noticable improvement?

Best Answer

If you want to take advantage of LIMIT to improve performance, you need

  • understand the data you are retrieving
  • proper indexing the correct sequence of columns
  • take responsibility for refactoring the query
  • using LIMIT before JOIN

These principles can go a long way if you can orchestrate them.

I learned these concepts by watching this YouTube Video (listen carefully through the French accent)

I used those concepts to answer a very tough StackOverflow question about getting the top 40 articles from some tables : May 12, 2011 : Fetching a Single Row from Join Table.

In my answer to that question (May 16, 2011), I wrote the following query and tested it thoroughly:

SELECT
  AAA.author_id,
  AAA.date_created,
  IFNULL(BBB.title,'<NO_TITLE>') title,
  IFNULL(CCC.filename,'<NO-IMAGE>') filename,
  IFNULL(CCC.date_added,'<NO-IMAGE-DATE>') image_date
FROM
(
  SELECT
    AA.id,
    AA.date_added,
    BB.author_id,
    BB.date_created
  FROM
  (
    SELECT
      A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added
      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A
      LEFT JOIN article_images B ON A.id = B.article_id
      GROUP BY A.id
  ) AA
  INNER JOIN articles BB USING (id)
) AAA
LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id
LEFT JOIN article_images CCC
ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added)
ORDER BY AAA.date_created DESC;

Please notice the line in the query with the LIMIT

      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A

This subquery is buried three levels deep. This allowed me to get the last 40 articles using LIMIT. Then, I performed the necessary JOINs afterwards.

LESSONS LEARNED

  • Doing LIMIT inside subqueries may not always be the answer because of the cardinality of indexes, the data content, and the result set size from the LIMIT. If you have all your "ducks in a row" (Have the four principles in mind for your query), you can get surprisingly good results.
  • Make your queries as simplistic as possible when doing LIMIT by gathering keys only.
Related Question