If I use query this in MySQL:

SELECT * FROM table WHERE some_col=something ORDER BY some_col LIMIT 10000, 10

It will scan 10010 rows, because it is not able to find row number 10000 without it. Are there RDBMS that can perform such tasks faster, straight getting to 10000 row without scanning all 10000 (with WHERE clauses like in the example above)?

  • Use indexes?... – Vérace Oct 11 '15 at 22:54
  • 1
  • 2
    Rows in an SQL database don't have "numbers", especially after you ORDER BY some_col. If somebody gave you a (big) bucket of golf balls, how would you find the 10000th ball (ordered by brand alphabetically)? – mustaccio Oct 12 '15 at 0:18
  • Try without select * – vasin1987 Oct 12 '15 at 4:19

Most RDBMS mostly use BTrees for indexes. These hold no summary information internally about the fan-out or total number of descendents. So the only way to get to a desired offset within the data is to start at the beginning a step through.

If you were able to explicitly define a sequencing column within your table the RDBMS may be able to use an index on it to more quickly get to your offset (no guarantees; which indexes the optimiser chooses to use is a complex topic). The query would then become

SELECT * FROM table 
WHERE some_col = something 
AND sequence_col > 10000        -- may get an index seek because of this, maybe not
ORDER BY sequence_col LIMIT 10  -- ordering by the new column

There are many risks associated with this, including

  • maintaining separate sequence columns for each desired sort order
  • concurrent changes in the data while a read is in progress
  • holes appearing in the sequence

Usually you are much better off simply indexing the existing business-required columns in an appropriate way and implementing pagination. @Vladimir has given a link for this.

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.