Back to the original question: In this case, since the main table is a normal rowid table then the interior pages of the B tree that stores it are only going to contain the rowid part of the table's records, and you have to go all the way down to the leaves to get the rest of each record. In the (covering) index B tree every page has all the needed data, so you don't "waste" time accessing those extra interior pages.
In the same vane I assume DRH's random ordering would be only random by page of results. If you have 100+ million records in a table then keeping track of which ones you've randomly picked so far would cripple systems with the tracking requirements and with the slowdown of skipping all over the file. Shuffling the order is one thing, killing performance is another. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bart Smissaert Sent: Thursday, January 12, 2017 12:03 PM To: SQLite mailing list Subject: Re: [sqlite] Why this query plan? > Because the index is smaller than the main table. Less disk I/O. Yes and that is the one (and only one) interesting thing I found from this thread. So you could benefit from an index for reasons other than the usual reasons eg assisting the where clause. RBS _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
