Consider a table with a single
id which has a
PRIMARY KEY index:
CREATE TABLE test (id INT PRIMARY KEY)
Now consider queries like
SELECT COUNT(*) FROM test SELECT * FROM test ORDER BY id LIMIT ?, 1
(using MySQL syntax for the
? is the offset)
It seems that despite there being an index on the table (on the
id), these queries (counting rows or selecting a row at an offset) all take linear time because they have to do full or partial table scans; there is no index on the "ranks" / positions of the rows in the sorted order which would facilitate counting or getting elements by rank (compare to Redis' sorted set, which allows doing operations by rank efficiently).
The problem is that the (typically B-Tree) index is constrained to just
id and is not at all "augmented" to include counts / ranks. I've looked at SQLite, MySQL / MariaDB and PostgreSQL, and none of these DBMS's seem to allow augmenting the index to include counts / ranks.
My questions are:
- Is it possible to augment the B-Tree indexes SQLite, MySQL / MariaDB or PostgreSQL use to include counting?
- If not, is there another relational DBMS which uses SQL as its query language which supports such indexes?