Query Optimization in SQLite. We use rowid

During a recent query optimization, I came across a description of how SQLite works with rowid. In short: each table has an int64 rowid column whose value is unique for each record in the table. You can view the value by the name "rowid" and in the request * it is not shown.

Entries are stored as a B-tree by rowid. And this makes rowid search and selection very fast. Twice as fast as primary key or indexed field. As I understand it, a search on an indexed column is a search on the B-tree, as a result of which we find rowid. And already having rowid - we search for the necessary record.

The obvious question begs: how to make rowid and our PRIMARY KEY match? Very simple, maybe they already coincide, but you don’t know about it. :) It is enough to declare your PRIMARY KEY field in one of the following ways:

CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

There are cases when the logic suggests that PRIMARY KEY and rowid should match, but this is not so. For instance:

CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
CREATE TABLE t(x INT PRIMARY KEY, y, z);

The second case is especially interesting, because INT is considered an alias for INTEGER and the behavior should be the same, but there it was. I got caught on this error. :) If you began to doubt whether your PRIMARY KEY and rowid values ​​coincide, you can simply check this:

SELECT rowid, x FROM t;

SQLite in the column names will write x, x (2), i.e. instead of rowid, the name of the column with which rowid is associated will be indicated. The values, if they are in the table, will match for these columns. It is also worth mentioning that if the table already has a column with the name “rowid” - this does not guarantee an association, you simply lose the ability to query the value of the rowid system column.

Have a nice optimization.