At 15:20 28/11/2003, [EMAIL PROTECTED] wrote:
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 opcodep1 p2 p3
-- --
0 ColumnName0 0 name
1 ColumnName1 0 title
2 Integer 1 0
3 OpenRead 0 3 xx
4 Integer 1 0
5 OpenRead 1 4 xx_y
6 String0 0 fred
7 MakeKey 1 0 t
8 MemStore 0 0
9 MoveTo1 18
10MemLoad 0 0
11IdxGT 1 18
12IdxRecno 1 0
13MoveTo0 0
14Column0 0
15Column0 1
16Callback 2 0
17Next 1 10
18Close 0 0
19Close 1 0
20Halt 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 opcodep1 p2 p3
-- -- ---
0 ColumnName0 0 name
1 ColumnName1 0 title
2 Integer 1 0
3 OpenRead 0 3 xx
4 Rewind0 12
5 Column0 1
6 String0 0 fred
7 StrNe 1 11
8 Column0 0
9 Column0 1
10Callback 2 0
11Next 0 5
12Close 0 0
13Halt 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]
