> By default, each SQLite table row has a unique rowId, which works like a primary key if one isn’t explicitly defined.
It actually uses rowid even if you have a primary key.
You should try visualizing the primary key index for a WITHOUT ROWID table. Those indexes are my favourite
> Both Indexes look similar, but the second Index, with fewer Pages, should be faster.
Less nodes doesn’t really mean “faster”. The most important is the height of the tree.
The second most important is what happens when you find your value in the index. Do you need to load the rest from a separate table(rowid)? Or is the data just there for you (without rowid)? Especially range queries (aka where 50<= col <=100)
> I wanted to see how a database management system (DBMS) stores an index in both disk and memory, and how it searches through an Index...I chose SQLite for my experiments
SQLite is a bit of an outlier in how it handles...everything, but even more so in query processing. SQLite tends to favor simplicity over performance, which causes it to implement things differently than every other DB I've worked with. You have to understand - SQLite isn't competing with other databases. It's competing with JSON and XML files for persistent storage. This means that how it implements anything tells you practically nothing about how a real database would do something.
The term "indexes" serves both as the third-person singular present tense of the verb "to index" and as a plural noun form of "index." In contrast, "indices" is the traditional plural form of "index," particularly prevalent in mathematical and scientific contexts. While "indexes" is commonly used in general English, "indices" is often preferred in technical fields to maintain linguistic precision. Employing "indices" in such contexts helps distinguish between the action of indexing and the plural form of index, thereby enhancing clarity.
SQLite Index Visualization
(mrsuh.com)186 points by mrsuh 15 hours ago | 17 comments
Comments
> By default, each SQLite table row has a unique rowId, which works like a primary key if one isn’t explicitly defined.
It actually uses rowid even if you have a primary key.
You should try visualizing the primary key index for a WITHOUT ROWID table. Those indexes are my favourite
> Both Indexes look similar, but the second Index, with fewer Pages, should be faster.
Less nodes doesn’t really mean “faster”. The most important is the height of the tree.
The second most important is what happens when you find your value in the index. Do you need to load the rest from a separate table(rowid)? Or is the data just there for you (without rowid)? Especially range queries (aka where 50<= col <=100)
SQLite is a bit of an outlier in how it handles...everything, but even more so in query processing. SQLite tends to favor simplicity over performance, which causes it to implement things differently than every other DB I've worked with. You have to understand - SQLite isn't competing with other databases. It's competing with JSON and XML files for persistent storage. This means that how it implements anything tells you practically nothing about how a real database would do something.