Surely enough, this SQL:
SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,
t4.category_id, t5."name"
FROM rental t2
LEFT OUTER JOIN inventory t1
ON ( t1.inventory_id = t2.inventory_id )
LEFT OUTER JOIN film t3
ON ( t3.film_id = t1.film_id )
LEFT OUTER JOIN film_category t4
ON ( t4.film_id = t1.film_id )
LEFT OUTER JOIN category t5
ON ( t5.category_id = t4.category_id ) ;
generates the desired xBestIndex call:
jmpvtab BEST INDEX: Table: inventory nConstraints: 3
CONST[0]: 0 (inventory_id) = Usable
CONST[1]: 1 (film_id) = Usable
CONST[2]: 1 (film_id) = Usable
allowing the "first constraint" heuristic to be successful.
So, in the case where my application is generating SQL, I can fix it so that
the ON is generated in the helpful order. But I also let users type in their
own SQL.
Would it be too much to ask SQLite to determine <needed> vs. <supplied> based
on context rather than inferring based on position? I'm looking at SQL In a
Nutshell, 3rd Edition, page 353, and the JOIN syntax example shows the ON
statements in the order SQLite dislikes.
Thanks very much!
Eric
-----Original Message-----
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:[email protected]] On Behalf Of Hick Gunter
Sent: Monday, June 08, 2015 10:21 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Can you try changing LHS and RHS in the first ON expression?
The older, larger query has inventory_id on the LHS and film_id on the RHS. Now
you have all fields on the RHS.
It would seem the QP may be inferring <ltable> LEFT OUTER JOIN <rtable> ON
(<needed> = <supplied>) And placing the <needed> fields first in the argument
list And adding the <supplied> fields only for symmetry reasons (they can never
be used, because they point the "wrong way")
-----Urspr?ngliche Nachricht-----
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Montag, 08. Juni 2015 15:55
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler.
The example query from that thread was:
SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title,
t3."name" AS Category, t4.customer_id, t4.store_id, t4.email,
t5.length
FROM rental t1
LEFT OUTER JOIN inventory t8
ON ( t8.inventory_id = t1.inventory_id )
LEFT OUTER JOIN customer t4
ON ( t4.customer_id = t1.customer_id )
LEFT OUTER JOIN film_category t7
ON ( t7.film_id = t8.film_id )
LEFT OUTER JOIN film t5
ON ( t5.film_id = t8.film_id )
LEFT OUTER JOIN category t3
ON ( t3.category_id = t7.category_id )
LEFT OUTER JOIN film_actor t6
ON ( t6.film_id = t5.film_id )
LEFT OUTER JOIN actor t2
ON ( t2.actor_id = t6.actor_id ) ;
The "money" call to xBestIndex in that case looked like this:
jmpvtab BEST INDEX: Table: inventory nConstraints: 3
CONST[0]: 0 (inventory_id) = Usable
CONST[1]: 1 (film_id) = Usable
CONST[2]: 1 (film_id) = Usable
In response to this, I originally promised to create an index using all 3
constraints (two if which are the same), but SQLite rejected that and did a
table scan instead, because it really only needed an index based on
inventory_id. So the heuristic we ended up with, which did fix this specific
query, was (from Gunter):
> If you know nothing about a table except for the names of the fields
> and the number of rows, then you are best off choosing the first
> constraint only. (rows = cost = log n)
Well, now I have a counter-example. This time, the SQL looks like this:
SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title,
t4.category_id, t5."name"
FROM rental t2
LEFT OUTER JOIN inventory t1
ON ( t2.inventory_id = t1.inventory_id )
LEFT OUTER JOIN film t3
ON ( t3.film_id = t1.film_id )
LEFT OUTER JOIN film_category t4
ON ( t4.film_id = t1.film_id )
LEFT OUTER JOIN category t5
ON ( t5.category_id = t4.category_id ) ;
For whatever reason, the "money" call to xBestIndex in this case looks like
this:
jmpvtab BEST INDEX: Table: inventory nConstraints: 3
CONST[0]: 1 (film_id) = Usable
CONST[1]: 1 (film_id) = Usable
CONST[2]: 0 (inventory_id) = Usable
The order of the constraints is different! So, using the "first constraint"
heuristic, I commit to indexing based on film_id, but indexing inventory on
film_id not helpful for this query. SQLite sees that inventory is indexed on
film_id and decides to use table scan for inventory, and it's game over.
If SQLite calls to xBestIndex do not in some way convey which constraints
matter and which ones don't, I don't see how I can use virtual tables.
Here is the WhereTrace, in case it might help:
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200
0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200
---- begin solver. (nRowEst=0)
New 0 cost=216,200 order=0
---- after round 0 ----
0 cost=216 nrow=200 order=0
---- begin solver. (nRowEst=201)
New 0 cost=216,200 order=1
---- after round 0 ----
0 cost=216 nrow=200 order=1 rev=0x0
---- Solution nRow=200 ORDERBY=1,0x0
0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
---- begin solver. (nRowEst=0)
New 0 cost=271, 43 order=0
Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=0
---- begin solver. (nRowEst=181)
---- sort cost=239 (1/1) increases cost 271 to 272
New 0 cost=272, 43 order=0
Update 1 cost=216,180 order=1 was 0 cost=272, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=1 rev=0x0
---- Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
---- begin solver. (nRowEst=0)
New 0 cost=271, 43 order=0
Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=0
---- begin solver. (nRowEst=181)
---- sort cost=239 (1/1) increases cost 271 to 272
New 0 cost=272, 43 order=0
Update 1 cost=216,180 order=1 was 0 cost=272, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=1 rev=0x0
---- Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
---- begin solver. (nRowEst=0)
New 0 cost=271, 43 order=0
Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=0
---- begin solver. (nRowEst=181)
---- sort cost=239 (1/1) increases cost 271 to 272
New 0 cost=272, 43 order=0
Update 1 cost=216,180 order=1 was 0 cost=272, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=1 rev=0x0
---- Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
---- begin solver. (nRowEst=0)
New 0 cost=271, 43 order=0
Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=0
---- begin solver. (nRowEst=181)
---- sort cost=239 (1/1) increases cost 271 to 272
New 0 cost=272, 43 order=0
Update 1 cost=216,180 order=1 was 0 cost=272, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=1 rev=0x0
---- Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
---- begin solver. (nRowEst=0)
New 0 cost=271, 43 order=0
Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=0
---- begin solver. (nRowEst=181)
---- sort cost=239 (1/1) increases cost 271 to 272
New 0 cost=272, 43 order=0
Update 1 cost=216,180 order=1 was 0 cost=272, 43 order=0
---- after round 0 ----
1 cost=216 nrow=180 order=1 rev=0x0
---- Solution nRow=180 ORDERBY=1,0x0
1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180
*** Optimizer Finished ***
*** Optimizer Start ***
---- Solution nRow=1
0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1
*** Optimizer Finished ***
*** Optimizer Start ***
constraint[0]: col=2 termid=0 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=2.5741e+08
estimatedRows=16044
add: * 0.01.00 t2 (-999,0) f 00400 N 0 cost 0,279,139
constraint[0]: col=2 termid=0 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=67470
estimatedRows=4
add: * 0.01.03 t2 (0,1) f 00400 N 1 cost 0,160,20
constraint[0]: col=1 termid=5 op=2 usabled=0
constraint[1]: col=1 termid=6 op=2 usabled=0
constraint[2]: col=0 termid=7 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
usage[1]: argvIdx=0 omit=0
usage[2]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=2.09856e+07
estimatedRows=4581
add: * 1.02.01 t1 (-999,0) f 00400 N 0 cost 0,243,120
constraint[0]: col=1 termid=5 op=2 usabled=1
constraint[1]: col=1 termid=6 op=2 usabled=1
constraint[2]: col=0 termid=7 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
usage[1]: argvIdx=0 omit=0
usage[2]: argvIdx=0 omit=0
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=16770.9
estimatedRows=3
add: * 1.02.0f t1 (0,1) f 00400 N 1 cost 0,140,16
constraint[0]: col=0 termid=1 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=1e+06
estimatedRows=1000
add: * 2.04.03 t3 (-999,0) f 00400 N 0 cost 0,199,99
constraint[0]: col=0 termid=1 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=3000
estimatedRows=3
replace: * 2.04.03 t3 (-999,0) f 00400 N 0 cost 0,199,99
add: * 2.04.03 t3 (0,1) f 00400 N 1 cost 0,115,16
constraint[0]: col=0 termid=2 op=2 usabled=0
constraint[1]: col=1 termid=4 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
usage[1]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=1e+06
estimatedRows=1000
add: * 3.08.07 t4 (-999,0) f 00400 N 0 cost 0,199,99
constraint[0]: col=0 termid=2 op=2 usabled=1
constraint[1]: col=1 termid=4 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
usage[1]: argvIdx=0 omit=0
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=3000
estimatedRows=3
replace: * 3.08.07 t4 (-999,0) f 00400 N 0 cost 0,199,99
add: * 3.08.07 t4 (0,1) f 00400 N 1 cost 0,115,16
constraint[0]: col=0 termid=3 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=256
estimatedRows=16
add: * 4.10.0f t5 (-999,0) f 00400 N 0 cost 0,80,40
constraint[0]: col=0 termid=3 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=19.2659
estimatedRows=1
replace: * 4.10.0f t5 (-999,0) f 00400 N 0 cost 0,80,40
add: * 4.10.0f t5 (0,1) f 00400 N 1 cost 0,42,0
0 0.01.00 t2 (-999,0) f 00400 N 0 cost 0,279,139
1 0.01.03 t2 (0,1) f 00400 N 1 cost 0,160,20
2 1.02.01 t1 (-999,0) f 00400 N 0 cost 0,243,120
3 1.02.0f t1 (0,1) f 00400 N 1 cost 0,140,16
4 2.04.03 t3 (0,1) f 00400 N 1 cost 0,115,16
5 3.08.07 t4 (0,1) f 00400 N 1 cost 0,115,16
6 4.10.0f t5 (0,1) f 00400 N 1 cost 0,42,0
---- begin solver. (nRowEst=0)
New 0 cost=279,139 order=0
---- after round 0 ----
0 cost=279 nrow=139 order=0
New 02 cost=382,259 order=0
---- after round 1 ----
02 cost=382 nrow=259 order=0
New 024 cost=389,275 order=0
---- after round 2 ----
024 cost=389 nrow=275 order=0
New 0245 cost=400,291 order=0
---- after round 3 ----
0245 cost=400 nrow=291 order=0
New 02456 cost=400,291 order=0
---- after round 4 ----
02456 cost=400 nrow=291 order=0
---- Solution nRow=291
0 0.01.00 t2 (-999,0) f 00400 N 0 cost 0,279,139
2 1.02.01 t1 (-999,0) f 00400 N 0 cost 0,243,120
4 2.04.03 t3 (0,1) f 00400 N 1 cost 0,115,16
5 3.08.07 t4 (0,1) f 00400 N 1 cost 0,115,16
6 4.10.0f t5 (0,1) f 00400 N 1 cost 0,42,0
*** Optimizer Finished ***
Thanks,
Eric
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
