MySQL Forums
Forum List  »  General

Re: Using LIMIT in a SELECT statement which does a JOIN
Posted by: Rick James
Date: July 03, 2009 02:48PM

LIMIT is logically the last thing done. However, with SQL_CALC_FOUND_ROWS, it will then logically do a second query, wherein it short circuits whatever it can (such as fetching fields) to go faster.

The big question is whether the query be figured out by looking at only OFFSET+LIMIT rows. Roughly speaking, if the columns mentioned in the WHERE and ORDER BY are all in one table, you have a fighting chance of doing the query fast. If not, you probably have to collect all, sort all, finally apply the offset and limit.

Often a JOIN with ORDER BY and LIMIT has to fetch all the possible rows. This is because it usually cannot do the ORDER BY soon enough. A LIMIT without and ORDER BY is kinda dumb -- you can't always predict which rows you will get.

The following SELECT stands a chance of needing to read only 30 rows; it would benefit from an index on (B,E).
Select  t1.A, t2.D
    FROM  t1
    INNER JOIN t2  ON t1.C = t2.C
    WHERE  t1.B = 1
    ORDER BY t1.E   # note: I added this
    LIMIT  0,30     # note: I changed offset from 1 to 0
Adding SQL_CALC_FOUND_ROWS would force it to probe "all" of both tables -- it needs to verify that there is a matching C. But it can save some effort by not having to fetch A and D.

Now it is likely to have to read "all" of both tables:
Select  t1.A, t2.D
    FROM  t1
    INNER JOIN t2  ON t1.C = t2.C
    WHERE  t1.B = 1
    ORDER BY t2.E   # note: Now using the other table!
    LIMIT  0,30

With an offset such as LIMIT 60,30 it has to fetch 90 rows, then toss 60 and deliver 30. (Or it has to fetch all the rows, sort them, then skip 60 and deliver 30.)

I deviated from your samples, but hope I explained things well enough for you.

Options: ReplyQuote


Subject
Written By
Posted
Re: Using LIMIT in a SELECT statement which does a JOIN
July 03, 2009 02:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.