Are there RDBMS which can count rows fast?
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 &gt; 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.