CHAPTER 6 – SQLite – Tuning

We already saw that you can speed up large amounts of inserts by encapsulating the queries into a transaction. But, there are a few more tricks that we can do. Usually, when inserting a lot of data into the data- base, we're not interested in how many changes there were in the result set. SQLite allows you to turn off the counting of changes, which obviously improves speed during insertion. You can instruct SQLite not to count changes by running the following SQL query: PRAGMA count_changes = 0 For example, with $db->query("PRAGMA count_changes = 0"); Another trick is to change the way SQLite flushes data to disk. With the synchro- nous pragma, you can switch between the following modes, as shown in Table 6.13. Table 6.13 "PRAGMA Synchronous" Options Mode Description OFF SQLite will not flush written to disk at all; it's up to the operating system to handle this. ON/NORMAL (default) In this mode, SQLite will make sure the data is committed to disk by issuing the fsync() system call once in a while. FULL SQLite will now issue extra fsync()s to reduce the risk of corruption of the data in case of a power loss. In situations where there are a lot of reads from the SQLite database, it might be worthwhile to increase the cache size. Where the default is 2,000 pages (a page is 1,536 bytes), you can increase this size with the following query: PRAGMA cache_size=5000; This setting only has effect for the current session, and the value will be lost when the connection to the database is broken. If you want to persist this set- ting, you need to use the default_cache_size pragma instead of just cache_size.