MySQL Forums
Forum List  »  InnoDB

Re: Offset on index details
Posted by: Rick James
Date: June 27, 2016 04:18PM

Let's dissect this:
SELECT id FROM Things WHERE cat=1000 ORDER BY id DESC LIMIT 5 OFFSET 30000;

1. Parse the query, open table `Things`, decide how to perform the query.

2. If there is an index on (cat, id) use the index: Reach into the index to the last 'row' with that `cat`.
3. Walk backward through the index from the last `id`, counting as you go.
4. Skip over 30000 'rows' in the index by counting them
5. Reach into the 'data' for the next 5 rows. Deliver (id) to the client.

Or...
2. If there is no INDEX(cat, ...), scan the table.
3. Read _all_ rows, collecting `id` into a temp table -- MEMORY preferred; MyISAM as a fallback. (There are a lot of details here.)
4. Sort the temp table.
5. Walk through, from the end, counting.
6. Skip over 30000 in the temp table by counting them.
7. Deliver the next 5 rows from the temp.

It cannot leap forward 30000 rows. Perhaps the main reason is that the rows are not "numbered" in any useful way. (`id` cannot be trusted to have no gaps.)

More on the inefficiency of OFFSET, plus some workarounds:
http://mysql.rjweb.org/doc.php/doc.php/pagination
The trick there is "remember where you left off".

Options: ReplyQuote


Subject
Views
Written By
Posted
1229
June 24, 2016 07:26AM
544
June 24, 2016 09:05AM
547
June 24, 2016 09:42AM
Re: Offset on index details
655
June 27, 2016 04:18PM
460
August 17, 2016 07:43PM


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.