> 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:
```
A:
for i = 0 to lastitem in Index A
read r from A
look-up r in Table T
read row r from T
do_whatever;
B:
for r = 0 to lastrow in Table T
read row r from T
do_whatever;
```
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!