On 11/20/19 1:26 PM, Simon Slavin wrote: > On 20 Nov 2019, at 6:11pm, Andy Bennett <andy...@ashurst.eu.org> wrote: > >> In past attempts at improving query performance these have been added to >> encourage it to use an index that it can do a SCAN thru' rather than the >> table that it would need to do a SEARCH thru'. > SQLite is not using the PRIMARY INDEX to immediately locate the appropriate > row, but is actually faster when you fake it into using a longer index ? > That's weird.
I think the issue is that the 'Primary Index' isn't really the primary index, but that the implied ROWID (since the table isn't WITHOUT ROWID, and the Primary Key isn't INTEGER PRIMARY KEY) will be the primary key. Thus a lookup of a row with his declared primary key is a two step lookup, find the key in the Unique index for the key, and then lookup the specified record by ROWID, if there is a index with the needed data, then the second lookup isn't needed. -- Richard Damon _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
