SQLite Forum

Query shape to get plan with SeekGE on a composite key
Login
Excellent - that's just what I was looking for.

Thanks, Richard!

David

```sql
sqlite> SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1,KeyPart2,KeyPart3,KeyPart4) > (1,x'abcdef',2,3) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
1|ABCDEF|3|4
sqlite> EXPLAIN QUERY PLAN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1,KeyPart2,KeyPart3,KeyPart4) > (1,x'abcdef',2,3) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
QUERY PLAN
`--SEARCH Test USING PRIMARY KEY ((KeyPart1,KeyPart2,KeyPart3,KeyPart4)>(?,?,?,?))
sqlite> EXPLAIN SELECT KeyPart1, hex(KeyPart2), KeyPart3, KeyPart4 FROM Test WHERE (KeyPart1,KeyPart2,KeyPart3,KeyPart4) > (1,x'abcdef',2,3) ORDER BY KeyPart1, KeyPart2, KeyPart3, KeyPart4 LIMIT 1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     39    0                    0   Start at 39
1     Noop           1     9     0                    0
2     Integer        1     1     0                    0   r[1]=1; LIMIT counter
3     OpenRead       2     2     0     k(4,,,,)       0   root=2 iDb=0; sqlite_autoindex_Test_1
4     Integer        1     2     0                    0   r[2]=1
5     Blob           3     3     0     ???             0   r[3]=??? (len=3)
6     Integer        2     4     0                    0   r[4]=2
7     Integer        3     5     0                    0   r[5]=3
8     IsNull         2     38    0                    0   if r[2]==NULL goto 38
9     SeekGE         2     38    2     4              0   key=r[2..5]
10      Integer        1     6     0                    0   r[6]=1
11      Column         2     0     7                    0   r[7]=Test.KeyPart1
12      Gt             8     29    7     BINARY-8       68  if r[7]>r[8] goto 29
13      ElseEq         0     16    0                    0
14      ZeroOrNull     7     6     8                    0   r[6] = 0 OR NULL
15      Goto           0     29    0                    0
16      Column         2     1     7                    0   r[7]=Test.KeyPart2
17      Gt             9     29    7     BINARY-8       65  if r[7]>r[9] goto 29
18      ElseEq         0     21    0                    0
19      ZeroOrNull     7     6     9                    0   r[6] = 0 OR NULL
20      Goto           0     29    0                    0
21      Column         2     2     7                    0   r[7]=Test.KeyPart3
22      Gt             10    29    7     BINARY-8       68  if r[7]>r[10] goto 29
23      ElseEq         0     26    0                    0
24      ZeroOrNull     7     6     10                   0   r[6] = 0 OR NULL
25      Goto           0     29    0                    0
26      Column         2     3     7                    0   r[7]=Test.KeyPart4
27      Gt             11    29    7     BINARY-8       68  if r[7]>r[11] goto 29
28      ZeroOrNull     7     6     11                   0   r[6] = 0 OR NULL
29      IfNot          6     37    1                    0
30      Column         2     0     12                   0   r[12]=Test.KeyPart1
31      Column         2     1     6                    0   r[6]=Test.KeyPart2
32      Function       0     6     13    hex(1)         0   r[13]=func(r[6])
33      Column         2     2     14                   0   r[14]=Test.KeyPart3
34      Column         2     3     15                   0   r[15]=Test.KeyPart4
35      ResultRow      12    4     0                    0   output=r[12..15]
36      DecrJumpZero   1     38    0                    0   if (--r[1])==0 goto 38
37    Next           2     10    0                    0
38    Halt           0     0     0                    0
39    Transaction    0     0     1     0              1   usesStmtJournal=0
40    Integer        1     8     0                    0   r[8]=1
41    Blob           3     9     0     ???             0   r[9]=??? (len=3)
42    Integer        2     10    0                    0   r[10]=2
43    Integer        3     11    0                    0   r[11]=3
44    Goto           0     1     0                    0
sqlite>
```