07bIndexing
Database Management
03 May 2026
Relax, you don’t have to sort
- SQL has a CREATE INDEX statement that will sort a column for you
- It uses an external merge sort
- It then creates a new data structure called a B-Tree (the B stands for balanced, not binary! This is nothing like a binary tree) to hold the result
- This approach, building a B-Tree, differs from most other database software, which usually offer options for different access patterns
- This keeps SQLite very small and predictable
- The tradeoff is that some specialized workloads like geospatial queries are either supported via virtual tables or not all
Should you index everything?
- Indexing speeds up reading but slows down writing
- Indexing consumes a lot of storage
- Use it only on frequently read columns and, even then, you may want to check whether queries run slowly before you do it
Indexing is overused
- People usually don’t check before they index because they believe that storage can grow infinitely
- Don’t be that person
- By the way, B-Trees are ubiquitous in databases and deserve separate study, which you would get in an advanced database class or, often, in a data structures class
END
Colophon
This slideshow was produced using quarto
Fonts are Roboto, Roboto Light, and Victor Mono Nerd Font
