The EXPLAIN Statement
|
| There is a way to compare how different indexes affect the speed of certain queries without having to perform endless benchmark tests. However, it requires a little knowledge of the inner workings of SQLite. The EXPLAIN command can be used to find out how an SQL query is parsed by SQLite and from that you can determine the way in which it will actually be executed. The output from EXPLAIN is a series of opcodes from the Virtual Database Engine, which we'll look at in more detail in Chapter 10, "General Database Administration." When using sqlite the .explain command can be used to make the format of the output of EXPLAIN more readable. The following example shows the opcodes used to process a query on t2 using a WHERE condition on the non-indexed num column. sqlite> .explain sqlite> EXPLAIN SELECT word FROM t2 WHERE num = 1234; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- ----------------------------------- 0 ColumnName 0 0 word 1 Integer 0 0 2 OpenRead 0 4 t2 3 VerifyCookie 0 2979 4 Rewind 0 11 5 Column 0 0 6 Integer 1234 0 1234 7 Ne 1 10 8 Column 0 1 9 Callback 1 0 10 Next 0 5 11 Close 0 0 12 Halt 0 0 To work out whether an index is used or not, knowing the actual meaning of all these opcodes is not necessary. If you compare the preceding output to that for a similar query on t3 where num is indexed, you'll see the name of the index in the p3 column alongside an OpenRead opcode. sqlite> EXPLAIN SELECT word FROM t3 WHERE num = 1234; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- ----------------------------------- 0 ColumnName 0 0 word 1 Integer 0 0 2 OpenRead 0 5 t3 3 VerifyCookie 0 2979 4 Integer 0 0 5 OpenRead 1 1077 t3_num_idx 6 Integer 1234 0 1234 7 NotNull -1 10 8 Pop 1 0 9 Goto 0 22 10 MakeKey 1 0 n 11 MemStore 0 0 12 MoveTo 1 22 13 MemLoad 0 0 14 IdxGT 1 22 15 RowKey 1 0 16 IdxIsNull 1 21 17 IdxRecno 1 0 18 MoveTo 0 0 19 Column 0 1 20 Callback 1 0 21 Next 1 13 22 Close 0 0 23 Close 1 0 24 Halt 0 0 Because table and column names from the query will also appear in the p3 column, it is a good idea to have a strong naming convention for your indexes so that they stand out in this output. In this case we used t3_num_idx, which tells us exactly which table and column the index applies to. We can use EXPLAIN to see which index SQLite chooses to use where two are availableremember that only one index can be used per table in a query. In the following examples, only the lines of output from EXPLAIN that correspond to an index name are given. sqlite> EXPLAIN SELECT * FROM t3 ...> WHERE num = 4000 AND word = 'soccer'; ... 6 OpenRead 1 1682 t3_word_idx ... So of the two indexes available on table t3, SQLite has chosen the index on t3.word to use for this query. Let's take a moment to recap what indexes are in place on these tables. You can always find this information by querying the sqlite_master table: sqlite> SELECT name, sql FROM sqlite_master ...> WHERE type = 'index'; name sql ---------------- ------------------------------------ t3_num_idx CREATE INDEX t3_num_idx ON t3(num) t1_word_idx CREATE INDEX t1_word_idx ON t1(word) t3_word_idx CREATE INDEX t3_word_idx on t3(word) In fact the index that SQLite will choose where more than one is equally suitable is the one that appears last in the sqlite_master tableusually the one most recently created. A brand new index will also take precedence over older indexes for the duration of the current SQLite session and the database schema has not been re-read. A clustered index is considered more suitable than an index on a single column if all the columns in the index form part of the WHERE clause. There may be occasions when you want to prevent use of the index SQLite would otherwise choose in order to force another index to be used. This is achieved by using an operator that does not affect the column's value. To make sure the index on t3.word is used, add zero to the num column: sqlite> EXPLAIN SELECT * FROM t3 ...> WHERE num+0 = 4000 AND word = 'soccer'; ... 6 OpenRead 1 1695 t3_word_idx ... Similarly, to force use of the index on t3.num, use the concatenation operator to append an empty string to word: sqlite> EXPLAIN SELECT * FROM t3 ...> WHERE num = 4000 AND word||'' = 'soccer'; ... 6 OpenRead 1 1077 t3_num_idx ... If multiple indexes can be used in a query, each one will appear in the output from EXPLAIN. The following example shows that indexes are also used for a LEFT OUTER JOIN. sqlite> EXPLAIN SELECT count(*) FROM t1 ...> LEFT OUTER JOIN t3 ON t1.num = t3.num ...> WHERE t1.word in ('apple', 'banana'); 11 OpenRead 2 927 t1_word_idx 15 OpenRead 3 2042 t3_num_idx Using TransactionsYou saw in Chapter 3, "SQLite Syntax and Use," that every change to the database must take place within a transaction, and that if one is not started explicitly with the BEGIN TRANSACTION command, SQLite will begin an implicit transaction whenever an INSERT, UPDATE, or DELETE statement is processed. However, taking the time to create your own transactions can be well worth the effort. Each transaction requires separate low-level operations to open and close the journal file, and this is an unnecessary overhead when a number of changes to the database can be made in a single transaction. Therefore rather than letting SQLite start several implicit transactions for you when performing a series of changes to the database, get into the habit of using BEGIN TRANSACTION and COMMIT TRANSACTION wherever such statements can be grouped together. Note If you are writing to a temporary table, the impact of using transactions is not so drastic. Because temporary tables are not designed to be stored beyond the current session, the disk writes are not flushed to the database file as regularly. The VACUUM StatementAny kind of disk access is faster if the data being read is contiguous on the drive, and fetching rows from SQLite is no exception. When records are deleted from a table, SQLite blanks out the space they used to occupy but does not remove it from the database file. The same applies when a table or index is dropped. Because additional disk space does not need to be reallocated to the database file, a future INSERT statement that can occupy this space is slightly quicker, but not all data records are the same size, and before long the database will become fragmented. The VACUUM command is SQLite's defragmenter. It works by systematically copying the contents of the database to a temporary fileleaving no empty spaceand then reloading the original file from this copy. The SQL syntax allows a table name or index name argument to be passed to VACUUM for backward compatibility with much older versions of SQLite, however any argument given is now simply ignored. Issuing a VACUUM command will always clean up the entire database. Tuning the Database ItselfThis chapter has mostly covered optimizations that can be performed on individual SQL statements. Another way to improve the performance of SQLite is to tune certain database parameters to suit your application. We will examine system-wide performance considerations in Chapter 10. |
|
