Query optimization in SQLite. Use rowid

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

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

This begs the obvious question: how to make the rowid and our PRIMARY KEY match? It's very simple, maybe they already coincide with you, but you do not 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 logic dictates that PRIMARY KEY and rowid should match, and this is not the case. For example:

 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, since INT is considered an alias for INTEGER and the behavior must be the same, but it was not there. I'm on this error and got caught. :) If you started to doubt if you have the same PRIMARY KEY and rowid values, you can just check this:

 SELECT rowid, x FROM t; 

SQLite in column names will write x, x (2), i.e. instead of rowid, the name of the column with which rowid is associated will be specified. Values, if any, in the table will be the same for these columns. Another thing to mention is that if there is already a column in the table with the name “rowid” - this does not guarantee associations, you simply lose the ability to query the value of the system column rowid.
')
Nice optimization.