I'm attempting to use the command-line SQLite to test the speed of certain "selects" and how writing them in different fashions affects speed
OK, can anyone explain (no pun intended!) what I should be looking for in what information "explain" returns? Are there any timing numbers in there I can look at?
I always look for the use of indices, and loops and things
create temp table xx (name varchar(30), title varchar(30)); create temp index xx_y on xx(name);
sqlite> explain select * from xx where name="fred"; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- -------- 0 ColumnName 0 0 name 1 ColumnName 1 0 title 2 Integer 1 0 3 OpenRead 0 3 xx 4 Integer 1 0 5 OpenRead 1 4 xx_y 6 String 0 0 fred 7 MakeKey 1 0 t 8 MemStore 0 0 9 MoveTo 1 18 10 MemLoad 0 0 11 IdxGT 1 18 12 IdxRecno 1 0 13 MoveTo 0 0 14 Column 0 0 15 Column 0 1 16 Callback 2 0 17 Next 1 10 18 Close 0 0 19 Close 1 0 20 Halt 0 0
This shows (step 5) that the index is being opened, steps 6-9 (I think) that a lookup on the index for 'fred' is being perfomed,
then step 11 checks if the current index value is bigger than 'fred', and jumps to step 18 if so
12-13 moves to the next index record
steps 14-15 get the data
step 17 moves to the next record in the index at step 10
So, this will iterate through the index 'xx_y' from 'fred' until the value of the index > 'fred'. (ie not much looping)
(I think)
Then
sqlite> explain select * from xx where title="fred"; addr opcode p1 p2 p3 ---- ------------ ---------- ---------- ----------- 0 ColumnName 0 0 name 1 ColumnName 1 0 title 2 Integer 1 0 3 OpenRead 0 3 xx 4 Rewind 0 12 5 Column 0 1 6 String 0 0 fred 7 StrNe 1 11 8 Column 0 0 9 Column 0 1 10 Callback 2 0 11 Next 0 5 12 Close 0 0 13 Halt 0 0
This doesn't open the index.
5-7 compares column 1 ('title') with the text 'fred', if it isn't that it jumps to step 11
8-9 gives the data
11 goes to the next record at step 5
So, this will iterate through the entire database looking for 'fred' (potentially lots of looping)
(Note I'm not entirely sure what everything means, but this is what I've surmised over time)
In general, in a loop, index operations are good, things like 'strne', 'ne' etc aren't as good because they probably operate more often.
You can't have timing information, because, the 'explain' doesn't look at the actual data available, so, if you just look at timing, my unindexed query above would probably look to be quicker, but in a large data set, the indexed query would actually probably be a lot quicker, because it'd have to go around the loop less times, even though the index operations themselves might well be slower than the plain comparisons.
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
