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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
