After digging deeper into indexes beyond their basic structure, I explored how data is structured, algorithms involved, and how indexes are stored in a database management system like SQLite. I experimented with SQLite as it’s widely used, easier to debug, and open-source. The indexes are stored in a B-Tree structure with fixed page sizes. I analyzed the index structure using C structures and wrote functions to output index data. Visualizing the index structure has its challenges, but with PHP’s ImageMagick extension, I was able to create an easily readable image. Further experiments included creating indexes with different sorts, expressions, NULL values, and multi-columns. Rebalancing and optimizing indexes can be achieved through VACUUM and REINDEX commands. Text and float-point data in indexes are stored accordingly. The overall work on analyzing SQLite indexes provided insights into how data is stored and accessed efficiently.
https://mrsuh.com/articles/2024/sqlite-index-visualization-structure/