On Thu, 28 Jan 2016 22:08:02 +0200 R Smith <rsmith at rsweb.co.za> wrote:
> I think you are misunderstanding the Pragma and the idea of automatic > indices. An automatic Index might be created on a table that doesn't > have an adequately assigned primary key. It might also be created during > a query (mostly SELECT or sub-SELECT) for which there is no useful Index > created by the table designer and the Query planner figures it will be > quicker to make an Index than to do table scans through the query. This > habit of creating indices during select queries can be forced to not > happen by setting the "PRAGMA automatic_index=0;", but this needs to > happen when you open the DB connection, or at a minimum, before you try > any query - not after the cursor is created, by that time the index > might already be made. (This is why you are not seeing any speed > improvement). Here is, from the documentation: https://www.sqlite.org/optoverview.html At the very bottom, is said: > Do not confuse automatic indexes with the internal indexes (having names like > "sqlite_autoindex_table_N") that are sometimes created to implement a PRIMARY > KEY constraint or UNIQUE constraint. The automatic indexes described here > exist only for the duration of a single query, are never persisted to disk, > and are only visible to a single database connection. Internal indexes are > part of the implementation of PRIMARY KEY and UNIQUE constraints, are > long-lasting and persisted to disk, and are visible to all database > connections. The term "autoindex" appears in the names of internal indexes > for legacy reasons and does not indicate that internal indexes and automatic > indexes are related. That's this legacy naming which causes confusion. -- Yannick Duch?ne
