SQLite Forum

Query planner fails to use obvious index
> Well, with all due respect, that is not quite correct either. And the cases where it is not true show when using an index could be faster than a b-tree guided table scan.

With even more due respect, there is no case in which scanning a table's information is done faster by scanning any index which references the table.

Your point is good though, but I think perhaps my point was misunderstood - I'm not talking about look-ups, they will mostly be faster[1], I'm specifically (and only) referring to table-scans. i.e. a process in which EVERY row of a table is visited and read.

A simple armchair experiment will show that between these tow algorithms:

for i = 0 to lastitem in Index A
    read r from A
    look-up r in Table T
    read row r from T

for r = 0 to lastrow in Table T
    read row r from T

That B MUST in principle always be faster.

If there exists a method or a sort of linking between indexes and tables that makes A faster in some way, then I'm a monkey's uncle, and that method should ALWAYS be used to store table-indexes in stead of whatever silly slow method now underlies the row-id.

[1] Further to the original point, even if you do use look-ups - 
If you draw two graphs depicting number of rows hit in a query, one for table-scan and one for number-of-Indexed-look-ups, both vs. cpu cycles, then:

- 1: The table-scan should be a near-perfectly flat line (because a scan always takes the same cycles to complete, unless we have a LIMIT clause or some way to know that we are done at any point), and 
- 2: The Indexed-lookup should start very low for a single look-up and then steadily grow faster until it crosses the line of the table-scan graph, but it MUST cross that line BEFORE number-of-row-hits = number-of-rows-in-table. There is no situation in which it can finish underneath that line.

Hoping that clarifies my point some!