YES, you can. Create analyze data: - load your database - analyze - export the sqlite_statn tables
Build a new database: - create new database - analyze - import the saved sqlite_statn tables 6.2 Manual Control Of Query Plans Using SQLITE_STAT Tables SQLite provides the ability for advanced programmers to exercise control over the query plan chosen by the optimizer. One method for doing this is to fudge the ANALYZE results in the sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 tables. That approach is not recommended except for the one scenario described in the next paragraph. For a program that uses an SQLite database as its application file-format, when a new database instance is first created the ANALYZE command is ineffective because the database contain no data from which to gather statistics. In that case, one could construct a large prototype database containing typical data during development and run the ANALYZE command on this prototype database to gather statistics, then save the prototype statistics as part of the application. After deployment, when the application goes to create a new database file, it can run the ANALYZE command in order to create the statistics tables, then copy the precomputed statistics obtained from the prototype database into these new statistics tables. In that way, statistics from large working data sets can be preloaded into newly created application files. -----Urspr?ngliche Nachricht----- Von: Jan Asselman [mailto:jan.asselman at iba-benelux.com] Gesendet: Dienstag, 03. M?rz 2015 15:27 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order? Thanks for answering both of my questions. I guess this is similar to the 'Skip-Scan Optimization' mentioned in the documentation. That is what I assumed and explains the difference in query performance. Scanning the table once is faster than scanning the table for each and every value of column 'a' (and the table is too large for any sort of cache to be useful)... I can try to see if the ANALYZE statement makes a difference. But it would only be useful if I can copy the 'impact' of this statement to other database files with equal table definitions. Because in my application, I am constantly creating new database file, filling them, and eventually deleting them. Can I copy the "statistics tables" from one database file to another? -----Original Message----- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: dinsdag 3 maart 2015 13:08 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] full table scan ignores PK sort order? The subquery is the index access (partial table scan), which is performed once for each and every value in your IN list for the column a (in effect, the IN list is transformed into an ephemeral table and joined to your test table). Since you did not declare an index for your primary key constraint, SQLite has to invent one. If you insert a representative data set and run ANALYZE then the query plan may well change to something that suits the shape of your data better. -----Urspr?ngliche Nachricht----- Von: Jan Asselman [mailto:jan.asselman at iba-benelux.com] Gesendet: Dienstag, 03. M?rz 2015 11:27 An: General Discussion of SQLite Database Betreff: Re: [sqlite] full table scan ignores PK sort order? Thank you! I think I get it: - primary key is nothing but a UNIQUE constraint (in my case comparable to a separate index == table with columns a, b and rowID) - the full table scan returns rows in rowID order, which is the order in which the rows were added to the table There is just one more thing I would like to understand: Most of my queries are in the form "SELECT * FROM test WHERE a == ? AND b < ?;" and use the primary key index so that the rows are returned in the expected order without using the ORDER BY statement. It's only a special case where the query "SELECT * FROM test WHERE b < ? AND c > ?;" is used. That's why I decided not to add a separate index to column b. What I was trying, in order to prevent a full table scan, is force the use of the primary key index for this query and have sqlite allow all possible values for column a: "SELECT * FROM test WHERE a IN (<<256 different values>>) AND b < ? AND c > ?;" The query plan confirms the use of the primary key index: 0, 0, 0, SEARCH TABLE data USING INDEX sqlite_autoindex_test_1 (a=? AND b<?) 0, 0, 0, EXECUTE LIST SUBQUERY 1 - Why is there a subquery? - For a query that reads all rows in the table, the throughput was a lot worse than executing a full table scan (25x slower). Is this behavior explainable? Very much appreciated! Jan Asselman _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
