Hi! Catching up with my old emails...
"Jocelyn" == Jocelyn Fournier <joce@doctissimo.fr> writes:
Jocelyn> Hi, Jocelyn> Following this discussion in 2007 : http://lists.mysql.com/internals/34287 Jocelyn> Is there any plan to implement such an optimisation in MariaDB ? (I Jocelyn> think a lot of web app using pagination could take benefit of such an Jocelyn> optimisation, although there are some workarounds to avoid big LIMIT for Jocelyn> pagination) Thanks for the reference to the old discussion, I had missed the original and it was interesting reading. The problem here is that if you do a lot of deletes of rows or updates of keys, it would be hard to impossible to efficiently store a position id for each row. However some of the storage engines have a direct position to rows. - For MyISAM (and Maria) with static lengths row, you can directly jump to row 'X'. - For Maria, each 'dynamic length row' has an ID that one can use for positioning (There may be 'holes' in the ID, but that could be taken care of). However, neither of the above would help if you want to have position based on an index. Exactly what problem is it that you want to solve ? I saw your question of: How to optimize select * from abc limit 1000,30? Can't you use the HANDLER calls for this ? (This allows you to freely read backwards/forwards on an index with limit) In this case you don't know exactly where you are in the table, but you can freely move 30 rows from your current position. If you could describe your application a bit, there is maybe ways to easily extend the handler interface to solve your problem... For example, knowing this would help: - Do you want rows in position order, not key order? - Do you need to read a row from a specific position (1000) or do you need to read rows backward/forward based on an old position? - Do you need to read rows backwards? (By the way, I don't understand Sergei's comment about read_set in this context) Regards, Monty