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.