Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
1  
I suggest you read this, for the cases that LIMIT improves efficiency: Optimizing LIMIT Queries –  ypercube Oct 23 '13 at 21:12

2 Answers 2

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.
share|improve this answer
    
So (A [LEFT] JOIN B) LIMIT 100 is equivalent to (A LIMIT 100) [LEFT] JOIN (B LIMIT 100)? Where [LEFT] JOIN means outer or inner join –  Jim Oct 23 '13 at 21:28
    
It's more like (A LIMIT 100) [LEFT] JOIN B. The idea is to use LIMIT to determine the size of the result set as early as possible. I also use LEFT JOIN instead of INNER JOIN because LEFT JOIN will preserve the order of the keys on the left side. –  RolandoMySQLDBA Oct 23 '13 at 21:29
    
@Jim No, they are not. Sometimes, they are, like this one: (A LEFT JOIN B) GROUP BY A.pk LIMIT 100 can usually be rewritten as (A LIMIT 100) LEFT JOIN B GROUP BY A.pk (no INNER JOIN here, with inner joins they would not be equivalent.) Rolando's example is exactly such a case. –  ypercube Oct 23 '13 at 21:32
    
@ypercube:So with inner joins isn't there something to do to benefit from LIMIT? –  Jim Oct 23 '13 at 21:35
    
I was referring to the rewrite strategy outlined by Rolando. A query with JOINs and LIMIT may benefit as well. Or not. It depends. –  ypercube Oct 23 '13 at 21:36

When a query is executed it first gets translated into a plan that is made up of several operators. There are two basic types of operators: Blocking and Non-Blocking. A Non-Blocking Operator retrieves a row (or a few rows) from its child or children for each row requested from it. A Blocking Operator on the other hand has to read in and process the entire row set of all its children before it can produce any output.

Sort is a typical Blocking Operator. So a select with order by does not benefit much from a limit. However, there are RDBMSs that can utilize a sorting algorithm that needs less memory and is faster when a limit clause is provided. It is in this case enough to just store the currently first n rows and move them out of memory as earlier rows come along. That can be a significant performance gain. However, I am not 100% sure that MySQL has that ability.

Either way, even a limit-sort still needs to process the entire input row set before it can produce the first output row. While this algorithm, if implemented, can speed up the sort, if the rest of the query is the most expensive part, the total execution time will not significantly improve because of a provided limit.

share|improve this answer
    
I am little confused with the answer. You mention about sort but group by also sorts doesn't it? So if for instance I removed the order by and stick with the group by, does your answer still apply? Or a different analysis is needed? –  Jim Oct 23 '13 at 20:38
    
Depending on the query and the indexes present, GROUP BY could potentially lead to a plan that is goes not contain blocking operators. –  Sebastian Meine Oct 23 '13 at 22:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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