✅ adding SQLite [Indexed] to a C# class property
Hi I'm trying to understand what it means to add an index to a column and how it speeds up querying. I asked ChatGPT about indexing but I still don't understand the process the SQLite does that makes querying faster.
When an index is created on a column in a table, it creates a separate data structure that stores the values of the indexed column in a sorted order. This data structure is known as a B-tree index.
ChatGPT:
"Let's say you have a table with a column named "age", and you create an index on that column. When you insert data into the table, the indexed column values will be stored in the B-tree index in sorted order.
Now, if you execute a query that filters records based on the "age" column, the database engine will consult the B-tree index to quickly locate the rows that match the filter criteria, instead of scanning the entire table.
For example, if you have a query that filters records where age is equal to 30, the database engine will search for the portion of the B-tree index that corresponds to the value 30. It will then use the pointers in that portion of the index to directly access the relevant rows in the table, rather than scanning the entire table sequentially."
3 Replies
it basically means the indexed columns are stored in a way that's faster to search compared to non-indexed columns
specifically by using a b-tree instead of doing a linear search
B-tree
In computer science, a B-tree is a self-balancing tree data structure that maintains sorted data and allows searches, sequential access, insertions, and deletions in logarithmic time. The B-tree generalizes the binary search tree, allowing for nodes with more than two children. Unlike other self-balancing binary search trees, the B-tree is well ...
oh ok so the column is turned into a b-tree and its used for querying