CODEWITHSUNDEEP
mysqlsqlspecifications
Number47
Number47

Reputation: 493

Fast selection of a block of following records

Is there an implementation of SQL database allowing to select from a table the block of n following records -- in respect of the index -- starting from a specified index, with performance O(n + log tables_size)? But also allowing adding a record in O(log tables_size). If so, how to do it?

I'm probably a dreamer but, is it possible with MySQL?

Upvotes: 0

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If id is the primary key on a table, then the following will return in order of the time needed to fetch the records, plus the initial index seek:

select t.*
from t
where id >= SOMEID
order by id
limit <n>;

Adding a record consists of two parts. The first part is finding available space and the second part is inserting into the index. A b-tree index should require O(log table_size) for an insert. If the pages are full and you are only inserting at the end of the table, then finding the right page is constant time.

In other words, if I understand your question correctly, primary key clustered indexes do exactly what you are asking for.

Upvotes: 1

Related Questions