SQLite Index Visualization
mrsuh.com
SQLite Index Visualization
1–10 of 28 posts
Re: SQLite Index Visualization
#2The website is so legible I want to read it.
Re: SQLite Index Visualization
#3> 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.
Re: SQLite Index Visualization
#4The website is so legible I want to read it.
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.
Re: SQLite Index Visualization
#5Great effort!
> 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)
Re: SQLite Index Visualization
#6Great effort! > 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 “faste…
> Less nodes doesn’t really mean “faster”. The most important is the height of the tree.
In isolation of a single access yes. But when frequently accessing an index overall size can be very important for cache hit rate.
Re: SQLite Index Visualization
#7The website is so legible I want to read it.
Yeah, such a relief to see content w/o super dense ad loads, etc. Very cool article.
Re: SQLite Index Visualization
#8The 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.
Re: SQLite Index Visualization
#9> 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…
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).
Re: SQLite Index Visualization
#10> 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…
SQLite is a real database engine. I guess what you mean is that SQLite is not competing with database servers.