3 ms·

I'd be very interested in this SQLite Necronomicon you're talking about :)

• thechao 5y ago
  I don't know if you're serious, but the `xBestIndex` function is supposed to be a literal description of the size & efficiency of the various tables. Lets say you have four tables: A,B,C are indexed (log access); table D is linear scan. Then, the `xBestIndex` function should return log(num-rows(X)) for A, B, and C; it should return num-rows(X) for D. The issue is that SQLite considers the entire table when doing query plans, rather than the specific query that's about to be performed; this means if D is especially short, then it'll choose D as the "driver" table, and then linearly scan A, B, and C. This is not the behavior is uses for its own internal tables. Instead, internal tables are log-scanned based off the "best" table. I suppose, what I'd really like is a strong guarantee that the plans SQLite compiled always used the index. I understand that there's N! possible plan orders for a join, so we can't consider all orders, but whatever mechanism is exposed through `xBestIndex` is just bonkers bad.
  • frabert 5y ago
    Thanks, I was serious and any insight from anyone who has more experience than me in this stuff is appreciated!