On Mon, Jan 8, 2018 at 12:33 PM, R Smith <ryansmit...@gmail.com> wrote:
> using any other index means a round-trip reading and hitting values in
> THAT index,
then returning and looking up the hit result in the rowid table index,
and then reading the pages(s) from it and extracting the data -
where during a table scan, all this round tripping is skipped.
Plus during a table-scan, you're reading the table pages "in-order", and
decoding the page only once,
for all rows on each page. While an index-scan read the index "in-order",
but the table pages "out-of-order",
since most consecutive rows (from the index) end up on different table
pages. The page-cache helps to
avoid too much IO (if big enough), but you must still decode the page for
each row to access one specific record inside it.
Sometimes the full-table-scan is the most efficient indeed. --DD
PS: But also note that a query with an order-by or group-by might still
prefer an index-scan to avoid sorting rows,
if the index already matches the requested order, offsetting the eventual
higher cost from the index-scan.
sqlite-users mailing list