Wilfried Mestdagh wrote:
Hi Bert,
'select distinct Name ' +
'from Rx ' +
'where RxDT >= ' + DT + ' ' +
'order by Name'
One thing is not yet clear to me. That is the 'distinct'. To have this
as fast as possible I have to make also a separate index on "RxDt,
Name". Is that correct ? (or the way around: "Name, RxDt" ?)
---
Rgds, Wilfried
http://www.mestdagh.biz
Excuse me, I did not see the distinct.
Best way to find out is to do explain
(just put the word in front of your query, it gives you the opcodes,
that are executed).
I did a lot work with opcodes in version 2.7x. As I recall, for the
distinct a temporary table was created in which every new query result
was serached, and if found, it was skipped, and if not found, kept, and
also added to the temporary table.
If it works this way, you do not need an index on name, to do an
disitinct on name.
Let's try it in version 3.xx
I did create a table and executed your query. It looks like a lot of
SQLite is still the same as it was in 2.7
OpenVirtual creates a (btree)table for storing the results, Distinct
test if the result record already is in the VirtualTable, if no go to
22, insert it, if yes fall through and goto 24, which gets the next record.
On opcode 1, it first goes to opcode 28, in 29 it tests the cookie, and
then goes back to opcode 2, where the query starts running.
It is a bit different, the cookietest used to be in the lower opcodes.
But you'll find your way. Anyway, your qquestion,, you do not need an
index on a field on which you do a distinct, even it is there, it will
not be used.
explanation of opcodes on: http://www.sqlite.org/opcode.html
0 OpenVirtual 1 0 keyinfo(1,BINARY)
1 Goto 0 28
2 Integer 0 0
3 OpenRead 0 2
4 SetNumColumns 0 2
5 Integer 0 0
6 OpenRead 2 3 keyinfo(1,BINARY)
7 Integer 1 0
8 NotNull -1 11
9 Pop 1 0
10 Goto 0 25
11 MakeRecord 1 0 n
12 MoveGe 2 25
13 RowKey 2 0
14 IdxIsNull 0 24
15 IdxRowid 2 0
16 MoveGe 0 0
17 Column 0 0
18 MakeRecord -1 0
19 Distinct 1 22
20 Pop 2 0
21 Goto 0 24
22 IdxInsert 1 0
23 Callback 1 0
24 Next 2 13
25 Close 0 0
26 Close 2 0
27 Halt 0 0
28 Transaction 0 0
29 VerifyCookie 0 2
30 Goto 0 2
31 Noop 0 0
