3 ms·
B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.
by fulafel 6y ago
B-Tree indexes are designed to work like this, to require a low number of IO operations. The index contains pointers to other places in the index.
- pudmaidai 6y agoSo the answer is “yes, it has to download the index first”? None of these comments answer my question.
- detaro 6y agoAt least the information describing what tables and indices there are and where to find them - and then it gets what it needs once a query is run. Just like sqlite would if running from a local file on disk.
- phiresky 6y agoThe B-Tree is a tree that in this case is perfectly balanced. So if you do a query with an index in a database it will fetch an logarithmic amount of data from the index and then a constant amount of data from the table. For the example the wdi_data table is 300MB and an index on it is 100MB in size. This index has a tree depth of 4 - which means SQLite has to read exactly 4 pages (4KiB) to get to the bottom of it and find the exact position of the actual row data. you can check the depth of the b-trees with `sqlite3_analyzer`.
- rakoo 6y agoEverything in SQLite is stored in B-Trees. Data or indexes. So you don't need to download the whole index first; you only need to download the necessary pages of the trees to access data, whether it's part of an index or actual data
- tehbeard 6y agoIt just needs to download the pages (x KB chunks) to traverse from the header to the particular index and table schema, a fraction of the whole data.
- fulafel 6y agoIt has to download some parts ("pages") of the index as the query execution proceeds, and some header/schema description pieces of it first before execution starts.