> 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.
> SQLite isn't competing with other databases. It's competing with JSON and XML files for persistent storage
It competes with both. its clearly used for local persistent storage. SO are quite a lot of other things. It also competes with other RDBMSes where a separate server process is not a requirement.
That does mean it serves very different requirements, its just that its use case are a lot wider than just replacing JSON and XML files and similar.
> It also competes with other RDBMSes where a separate server process is not a requirement.
If you casually list off the top DB's either by usage or by recent hotness then almost all of them will have a server, but you'll also find they're basically all not embedded DB's with exception to RocksDB.
Meh, it isn't really too far off from the way other DBMS servers handle storage and indexes. The principles are pretty identical (especially when sqlite operates in WAL mode).
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.
FWIW, I find the font size (I am on an iPhone) way too large, particularly as there is also important text in the diagrams and that text is much smaller, so while I feel a need to shove my phone away from my face to deal with the overly large body text I then have to keep pulling it back in to feel comfortable reading the diagrams, which feel out of place.
> 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)
In isolation of a single access yes. But when frequently accessing an index overall size can be very important for cache hit rate.
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.
It competes with both. its clearly used for local persistent storage. SO are quite a lot of other things. It also competes with other RDBMSes where a separate server process is not a requirement.
That does mean it serves very different requirements, its just that its use case are a lot wider than just replacing JSON and XML files and similar.
If you casually list off the top DB's either by usage or by recent hotness then almost all of them will have a server, but you'll also find they're basically all not embedded DB's with exception to RocksDB.
So what I've seen in Finland is people using "time series" for the plural and "time serie" for the singular.
All major RDBMS use the term "indexes".