maria-developers team mailing list archive

[Michael Widenius <monty@xxxxxxxxxxxx>]

Hi!

Catching up with my old emails...
>>>>> "Jocelyn" == Jocelyn Fournier <joce@xxxxxxxxxxxxx> 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