14

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?

1
12

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.
5
  • 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
2

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.

2
  • 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
0

In my case, I can say Yes, even if I (still) don't understand why.

SELECT g0_.id AS id_0, COUNT(a1_.id_tarifs) AS sclr_1
FROM groupe_jardinerie g0_
INNER JOIN articles_tarifs a1_
  ON (a1_.groupe_jardinerie_id = g0_.id)
WHERE g0_.centrale_id = 511
  AND a1_.date_fin_tarif >= '2018-01-29 10:46:35'
GROUP BY g0_.id;

(result set)

8 rows in set (**18.14 sec**)

Note the time : 18 seconds. Same request with a big LIMIT :

SELECT g0_.id AS id_0, COUNT(a1_.id_tarifs) AS sclr_1 
FROM groupe_jardinerie g0_
INNER JOIN articles_tarifs a1_
  ON (a1_.groupe_jardinerie_id = g0_.id)
WHERE g0_.centrale_id = 511 
  AND a1_.date_fin_tarif >= '2018-01-29 10:46:35'
GROUP BY g0_.id
LIMIT 100000000000;

(exact same result set)

8 rows in set (**1.32 sec**)

More than ten times faster !!!

EXPLAIN give the same result for both requests.

+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                     | key     | key_len | ref                          | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | a1_   | NULL       | ALL    | IDX_438010BBC10784EF                              | NULL    | NULL    | NULL                         | 795135 |    33.33 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | g0_   | NULL       | eq_ref | PRIMARY,IDX_9CA5CF6758A1D71F,IDX_9CA5CF67670C757F | PRIMARY | 4       | phs.a1_.groupe_jardinerie_id |      1 |    50.00 | Using where                                  |
+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+

LIMIT should interfer only to limit the result set (i.e., if I do a LIMIT 4, I got only the first 4 rows of the above result set).

3
  • terrifying, what version are you using and can you create a simplified test case? – Evan Carroll Jan 30 '18 at 16:56
  • 1
    Your answer does not prove any new benefit for LIMIT. Your 1st query runs in 18 seconds giving a result set. All the data in the 2nd query is already cached in the InnoDB buffer pool due to the first query, So of course the 2nd query has to be faster, Even if you restart mysql, run the 1st query, restart mysql, and run the 2nd query, you will get the same result. . Having a better result for LIMIT can only come from doing: 1) LIMIT before JOIN, 2) LIMIT in sort order ASC or DESC. – RolandoMySQLDBA Jan 30 '18 at 21:10
  • Thanks for your interest.Creating a simplified test case could be difficult. – Pierre-Olivier Vares Feb 1 '18 at 9:01

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.