On Wednesday, 2 January, 2019 16:58, Jonathan Moules
<jonathan-li...@lightpear.com> wrote:
>Gah, sorry. Another typo. I really should be more awake when I post
>to this list. The non-simplified code does have the item_id on the
>subquery (otherwise it simply wouldn't execute at all of course). So:
>SELECT *
> FROM item_info
> JOIN (
> select
> count(1) as num,
> item_id
> from users
> group by item_id)
> USING (item_id)
> where item_id = ?;
Now perhaps we are getting somewhere. So now what exactly is your complaint?
You have asked for the following join to occur:
JOIN this table, which you can see by the following command:
select * from item_info
with the table that you can see by the following command:
select count(1) as num, item_id from users group by item_id
using the common column item_id in both tables
and then to return only the row where the item_id is the specific one provided.
So, the ONLY way to solve this is to generate the table on the RHS of the join
first, and then join it to the item_info table, and then return only the row
you have asked for. This produces the following plan:
CREATE TABLE users (
item_id TEXT REFERENCES item_info (item_id)
NOT NULL
COLLATE NOCASE,
some_data TEXT
);
CREATE INDEX users__item_id__idx ON users (
item_id
);
CREATE TABLE item_info (
item_id TEXT PRIMARY KEY ON CONFLICT IGNORE
NOT NULL
COLLATE NOCASE,
more_data TEXT
);
.eqp full
SELECT *
FROM item_info
JOIN (
select
count(1) as num,
item_id
from users
group by item_id)
USING (item_id)
where item_id = ?;
QUERY PLAN
|--MATERIALIZE 1
| `--SCAN TABLE users USING COVERING INDEX users__item_id__idx (~1048576 rows)
|--SEARCH TABLE item_info USING INDEX sqlite_autoindex_item_info_1 (item_id=?)
(~1 row)
`--SCAN SUBQUERY 1 (~96 rows)
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 77 0 00 Start at 77
1 Integer 45 1 0 00 r[1]=45
2 Once 0 45 0 00 materialize
"subquery_1"
3 OpenEphemeral 1 2 0 00 nColumn=2
4 Noop 3 1 0 00
5 Integer 0 5 0 00 r[5]=0; clear abort
flag
6 Null 0 8 8 00 r[8..8]=NULL
7 Gosub 7 42 0 00
8 OpenRead 4 3 0 k(2,NOCASE,) 00 root=3 iDb=0;
users__item_id__idx
9 ColumnsUsed 4 0 0 1 00
10 Explain 10 0 0 SCAN TABLE users USING COVERING INDEX
users__item_id__idx (~1048576 rows) 00
11 Noop 0 0 0 00 Begin WHERE-loop0:
users
12 Rewind 4 28 10 0 00
13 Noop 0 0 0 00 Begin WHERE-core
14 Column 4 0 9 00 r[9]=users.item_id
15 Compare 8 9 1 k(1,NOCASE) 00 r[8] <-> r[9]
16 Jump 17 21 17 00
17 Move 9 8 1 00 r[8]=r[9]
18 Gosub 6 33 0 00 output one row
19 IfPos 5 45 0 00 if r[5]>0 then
r[5]-=0, goto 45; check abort flag
20 Gosub 7 42 0 00 reset accumulator
21 Integer 1 10 0 00 r[10]=1
22 AggStep 0 10 2 count(1) 01 accum=r[2]
step(r[10])
23 If 4 25 0 00
24 Column 4 0 3 00 r[3]=users.item_id
25 Integer 1 4 0 00 r[4]=1; indicate
data in accumulator
26 Noop 0 0 0 00 End WHERE-core
27 Next 4 13 0 01
28 Noop 0 0 0 00 End WHERE-loop0: users
29 Gosub 6 33 0 00 output final row
30 Goto 0 45 0 00
31 Integer 1 5 0 00 r[5]=1; set abort flag
32 Return 6 0 0 00
33 IfPos 4 35 0 00 if r[4]>0 then
r[4]-=0, goto 35; Groupby result generator entry point
34 Return 6 0 0 00
35 AggFinal 2 1 0 count(1) 00 accum=r[2] N=1
36 SCopy 2 11 0 00 r[11]=r[2]
37 SCopy 3 12 0 00 r[12]=r[3]
38 MakeRecord 11 2 10 00 r[10]=mkrec(r[11..12])
39 NewRowid 1 13 0 00 r[13]=rowid
40 Insert 1 10 13 08 intkey=r[13]
data=r[10]
41 Return 6 0 0 00 end groupby result
generator
42 Null 0 2 3 00 r[2..3]=NULL
43 Integer 0 4 0 00 r[4]=0; indicate
accumulator empty
44 Return 7 0 0 00
45 Return 1 0 0 00 end subquery_1
46 OpenRead 0 4 0 2 02 root=4 iDb=0;
item_info
47 ColumnsUsed 0 0 0 3 00
48 OpenRead 5 5 0 k(1,NOCASE) 02 root=5 iDb=0;
sqlite_autoindex_item_info_1
49 ColumnsUsed 5 0 0 1 00
50 Explain 50 0 0 SEARCH TABLE item_info USING INDEX
sqlite_autoindex_item_info_1 (item_id=?) (~1 row) 00
51 Noop 0 0 0 00 Begin WHERE-loop0:
item_info
52 CursorHint 5 0 0 EQ(c0,expr) 00
53 Variable 1 14 0 00 r[14]=parameter(1,)
54 IsNull 14 75 0 00 if r[14]==NULL goto 75
55 Affinity 14 1 0 B 00 affinity(r[14])
56 SeekGE 5 75 14 1 00 key=r[14]
57 IdxGT 5 75 14 1 00 key=r[14]
58 DeferredSeek 5 0 0 00 Move 0 to 5.rowid if
needed
59 Explain 59 0 0 SCAN SUBQUERY 1 (~96 rows) 00
60 Noop 0 0 0 00 Begin WHERE-loop1:
subquery_1
61 Column 5 0 15 00
r[15]=item_info.item_id
62 CursorHint 1 0 0 EQ(r[15],c1) 00
63 Rewind 1 75 0 00
64 Column 5 0 16 00
r[16]=item_info.item_id
65 Column 1 1 17 00
r[17]=subquery_1.item_id
66 Ne 17 73 16 (NOCASE) 51 if r[16]!=r[17]
goto 73
67 Noop 0 0 0 00 Begin WHERE-core
68 Column 5 0 18 00
r[18]=item_info.item_id
69 Column 0 1 19 00
r[19]=item_info.more_data
70 Column 1 0 20 00 r[20]=subquery_1.num
71 ResultRow 18 3 0 00 output=r[18..20]
72 Noop 0 0 0 00 End WHERE-core
73 Next 1 64 0 01
74 Noop 0 0 0 00 End WHERE-loop1:
subquery_1
75 Noop 0 0 0 00 End WHERE-loop0:
item_info
76 Halt 0 0 0 00
77 Transaction 0 0 3 0 01 usesStmtJournal=0
78 Goto 0 1 0 00
Which does exactly what you asked in the most efficient manner possible.
What is it that you think it ought to be doing instead?
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
