Hey, Gunter,
Thanks for taking the time to make such a thorough response to my question.
Yes, your query is arguably more correct, though our queries do produce
identical results. The SQL I included was generated by a general-purpose query
tool that automatically joins tables based on key relationships first and then
common variable names, so it doesn't always do what makes the most sense.
Your explanation of xBestIndex is very helpful. The data that I am projecting
into SQLite via virtual tables does not have the concept of keys, constraints,
or indexes, at least not currently. So, when SQLite presents me with multiple
constraints and asks for the best choice, I'm flying a bit blind. Also, I'm
using SQLite as part of the aforementioned general-purpose query tool; I'm not
just trying to optimize queries into this DVD rental sample data; I'm trying to
come up with general rules that will work reasonably well for arbitrary queries
against data that I don't know much about. I have implemented the strategy I
mentioned at the end of my last post (choose the first constraint, constructing
an index for it in the xFilter call), and it's doing orders of magnitude better
than what I had before (SQLite is using my indexes) and performs as well as
copying the data into SQLite and letting SQLite do everything, at least for the
small set of cases I've tried.
But then what about a query like this:
SELECT * FROM T1
LEFT OUTER JOIN T2 ON ( T2.a = T1.a ) AND ( T2.b = T1.b ) AND (
T2.c = T1.c );
xBestIndex will get called here for T1 with 3 constraints, c, b, and a, in that
order. In this case, though, it seems (to the uninitiated, at least ;-) that
the "best index" would be:
CREATE INDEX T1_all ON T1 (c, b, a);
(or would it?) but the xBestIndex call does not contain any information that
tells me that. To be able to know that, I would have to know independently
what the join conditions are. And I could make that knowledge available to my
xBestIndex implementation. But how much better would that be than an index on
c and partial scans to find the others?
(I tried this exact example, once creating an index on just T1.c and once
creating an index on T1 (c, b, a), and in both cases, my index was not used by
SQLite. So I don't think I understand what SQLite wants.)
I guess xBestIndex is saying "Tell me about indexes that you already have
available so I can take advantage of them", but in my case, I have *no*
indexes, but I am willing to make whatever indexes would be most helpful, if I
could just figure that out.
Thanks again,
Eric
-----Original Message-----
From: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter
Sent: Tuesday, May 19, 2015 3:20 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Eric
Working backwards from your query, I think your schema would be similar to
(foreign keys omitted)
CREATE TABLE rental ( rentalID INTEGER PRIMARY KEY NOT NULL, rental_date TEXT,
inventory_id INTEGER, customer_id INTEGER, ...); CREATE TABLE inventory (
inventory_id INTEGER PRIMARY KEY NOT NULL, film_id INTEGER, ...); CREATE TABLE
customer ( customer_id INTEGER PRIMARY KEY NOT NULL, first_name TEXT, last_name
TEXT,...); CREATE TABLE film ( film_id INTEGER PRIMARY KEY NOT NULL, title
TEXT, release_year INTEGER, length INTEGER, ...); CREATE TABLE actor ( actor_id
INTEGER PRIMARY KEY NOT NULL, first_name TEXT, last_name TEXT,...); CREATE
TABLE category ( category_id INTEGER PRIMARY KEY NOT NULL, name TEXT, ...);
CREATE TABLE film_actor ( film_id INTEGER, actor_id INTEGER,
UNIQUE(film_id,actor_id) ); CREATE TABLE film_category ( film_id INTEGER,
category_id INTEGER, UNIQUE (film_id,category_id) );
And your query should really be (note that film_actor and film_category are
joined to the film table)
SELECT t1.rental_date, t1.inventory_id, t1.customer_id, t2.film_id,
t2.store_id, t3.first_name AS cust_firstname,
t3.last_name AS cust_lastname, t3.email,
t6.category_id, t4.title, t4.release_year,
t4.length,
t4.rating, t4.rental_rate, t5.actor_id, t8.name
AS category,
t7.first_name AS actor_firstname, t7.last_name
AS actor_lastname
FROM rental10 t1
LEFT OUTER JOIN inventory t2
ON ( t2.inventory_id =
t1.inventory_id )
LEFT OUTER JOIN customer t3
ON ( t3.customer_id =
t1.customer_id )
LEFT OUTER JOIN film t4
ON ( t4.film_id = t2.film_id )
LEFT OUTER JOIN film_actor t5
ON ( t5.film_id = t4.film_id )
LEFT OUTER JOIN film_category t6
ON ( t6.film_id = t4.film_id )
LEFT OUTER JOIN category t8
ON ( t8.category_id =
t6.category_id )
LEFT OUTER JOIN actor t7
ON ( t7.actor_id = t5.actor_id
);
When xBestIndex is called, SQLite is asking for the single BEST access method,
given any subset of the passed constraints, not an array of possible methods.
Since the xxx_id fields are already UNIQUE, there is NO POINT in producing an
index with additional fields (other than maybe producing a convering index,
which is only supported for native tables - there is no equivalent method for
virtual tables).
Sticking with the inventory table, it is worth noting, that there may be
several distinct entries with identical film_id (it makes business sense to
have more than 1 copy of a film that rents well), only one of which will match
any given rental record.
SQLIte will call xBestIndex twice:
Once with no constraints, which is handled by rows = cost = n.
Once with two constraints, inventory_id and film_id; you now have several
choices (SQL equivalent shown):
- CREATE UNIQUE INDEX inventory_id ON inventory (inventory_id );
This is answered correctly by setting rows = 1, cost = log(n), and the
argvIndex = 1 and omit = 1 for the inventory_id constraint ONLY(!!!)
- CREATE INDEX inventory_film ON inventory (film_id);
This is answered correctly by setting rows = n / <distinct film ids>
(=the average number of copies of a film) , cost = rows + log(n) (= the cost of
locating the first row + the number of rows), and the argvIndex = 1 and omit =
1 for the film_id constraint ONLY(!!!).
- CREAT UNIQUE INDEX inventory_film ON inventory ( film_id, inventory_id );
This is only useful as a covering index for native tables; as a partial
table scan, it is equivalent to the previous non-unqiue index; as inventory_id
would be required for a key lookup, it would be a less efficient version of the
first index; and even worse, in the query shown you do not have film_id AND
inventory_id as known values AT THE SAME TIME, so SQLite would discrad this
index as USELESS anyway.
(which is precisely what is happening in your case - SQLite is finding that it
does not know t1.inventory_id AND t4.film_id AND t5.film_id AND t6.film_id,
which you state as required to use your index)
Gunter
PS: Unless you have a very clever way of creating an index on the fly (in less
than O(n log n) time), it is well worth the effort to determine which indexes
to maintain beforehand.
-----Urspr?ngliche Nachricht-----
Von: Eric Hill [mailto:Eric.Hill at jmp.com]
Gesendet: Montag, 18. Mai 2015 22:22
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?
Per Richard's request, I have produced the WhereTrace for the query that uses
virtual tables and the same query that is not using virtual tables. That
output is at the end of this message. Ultimately, I think my question is, how
should I respond to xBestIndex such that SQLite will perform the query as
efficiently with virtual tables as it does when I copy all the data into SQLite?
Hick asked:
> Are you setting the constraintUsage return parameters correctly?
I suspect not. I think I don't understand what to do when xBestIndex is passed
multiple constraints, because those are the cases when SQLite rejects my
indexes. And, from debugging, my indexes are being rejected because this
condition (at the tail end of whereLoopFindLesser()):
if( (p->prereq & pTemplate->prereq)==pTemplate->prereq /* (1) */
is false, so the costs I am returning never get considered.
Here is an example. For the inventory table, xBestIndex gets called twice,
each time with 4 constraints, 3 of which happen to be the same, film_id,
presumably because inventory.film_id is used in 3 different join constraints:
jmpvtab BEST INDEX: Table: inventory nConstraints: 4
CONST[0]: 0 (inventory_id) = Unusable
CONST[1]: 1 (film_id) = Unusable
CONST[2]: 1 (film_id) = Unusable
CONST[3]: 1 (film_id) = Unusable
Index NOT created: est. cost: 440393770484721
jmpvtab BEST INDEX: Table: inventory nConstraints: 4
CONST[0]: 0 (inventory_id) = Usable
CONST[1]: 1 (film_id) = Usable
CONST[2]: 1 (film_id) = Usable
CONST[3]: 1 (film_id) = Usable
Index created: est. cost: 1
The first time, all the constraints are marked unusable, so I set argvIndex and
omit to 0 for all four constraints and set the estimatedCost to a ridiculously
large number. The second time, all of the constraints are marked as usable.
My data does not have any pre-existing indexes; I'm willing to create whatever
indexes are needed to speed up the query. So I set omit to 1 and argvIndex to
1, 2, 3, and 4 respectively for the four constraints. This is clearly where I
am confusing SQLite.
Here is a simpler example, for the film_actor table. xBestIndex is again
called twice:
jmpvtab BEST INDEX: Table: film_actor nConstraints: 2
CONST[0]: 1 (film_id) = Unusable
CONST[1]: 0 (actor_id) = Unusable
Index NOT created: est. cost: 890034380901136
jmpvtab BEST INDEX: Table: film_actor nConstraints: 2
CONST[0]: 1 (film_id) = Usable
CONST[1]: 0 (actor_id) = Usable
Index created: est. cost: 1
Here again, in the second case, I set omit to 1 for both and set argvIndex to 1
for film_id and 2 for actor_id. What I am trying to tell SQLite is that I am
willing to make an index that sorts first by film_id and then by actor_id for
rows with the same film_id. But I'm thinking that's not what SQLite wants.
Since I'm going to have to create an index for whichever constraint that SQLite
is going to filter on, maybe I should just take the first constraint, set omit
to 1 and argvIndex to 1, and set omit and argvIndex to 0 for all other
constraints? And then set the cost to n(log n) where n is number of rows,
since that is the cost of making an index?
Thanks very much for your help. WhereTrace follows:
=====================================
WhereTrace when USING virtual tables:
=====================================
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=3 termid=12 op=2 usabled=0
constraint[1]: col=2 termid=13 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
usage[1]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=10000
estimatedRows=10
add: * 0.01.00 t1 (-999,0) f 00400 N 0 cost 0,132,33
constraint[0]: col=3 termid=12 op=2 usabled=1
constraint[1]: col=2 termid=13 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
usage[1]: argvIdx=2 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=1
add: * 0.01.07 t1 (0,3) f 00400 N 2 cost 0,0,0
constraint[0]: col=0 termid=0 op=2 usabled=0
constraint[1]: col=1 termid=9 op=2 usabled=0
constraint[2]: col=1 termid=10 op=2 usabled=0
constraint[3]: col=1 termid=11 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
usage[1]: argvIdx=0 omit=0
usage[2]: argvIdx=0 omit=0
usage[3]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=4.40394e+14
estimatedRows=4581
add: * 1.02.01 t2 (-999,0) f 00400 N 0 cost 0,490,120
constraint[0]: col=0 termid=0 op=2 usabled=1
constraint[1]: col=1 termid=9 op=2 usabled=1
constraint[2]: col=1 termid=10 op=2 usabled=1
constraint[3]: col=1 termid=11 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
usage[1]: argvIdx=2 omit=1
usage[2]: argvIdx=3 omit=1
usage[3]: argvIdx=4 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=3
add: * 1.02.3f t2 (0,f) f 00400 N 4 cost 0,0,16
constraint[0]: col=0 termid=1 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=1.28738e+11
estimatedRows=599
add: * 2.04.03 t3 (-999,0) f 00400 N 0 cost 0,370,92
constraint[0]: col=0 termid=1 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=2
replace: * 2.04.03 t3 (-999,0) f 00400 N 0 cost 0,370,92
add: * 2.04.03 t3 (0,1) f 00400 N 1 cost 0,0,10
constraint[0]: col=0 termid=2 op=2 usabled=0
constraint[1]: col=1 termid=8 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
usage[1]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=1e+12
estimatedRows=1000
add: * 3.08.07 t6 (-999,0) f 00400 N 0 cost 0,400,99
constraint[0]: col=0 termid=2 op=2 usabled=1
constraint[1]: col=1 termid=8 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
usage[1]: argvIdx=2 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=3
add: * 3.08.7f t6 (0,3) f 00400 N 2 cost 0,0,16
constraint[0]: col=0 termid=3 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=1e+12
estimatedRows=1000
add: * 4.10.0f t4 (-999,0) f 00400 N 0 cost 0,400,99
constraint[0]: col=0 termid=3 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=3
replace: * 4.10.0f t4 (-999,0) f 00400 N 0 cost 0,400,99
add: * 4.10.0f t4 (0,1) f 00400 N 1 cost 0,0,16
constraint[0]: col=1 termid=4 op=2 usabled=0
constraint[1]: col=0 termid=7 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
usage[1]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=8.90034e+14
estimatedRows=5462
add: * 5.20.1f t5 (-999,0) f 00400 N 0 cost 0,500,123
constraint[0]: col=1 termid=4 op=2 usabled=1
constraint[1]: col=0 termid=7 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
usage[1]: argvIdx=2 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=3
add: * 5.20.ff t5 (0,3) f 00400 N 2 cost 0,0,16
constraint[0]: col=0 termid=5 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=65536
estimatedRows=16
add: * 6.40.3f t8 (-999,0) f 00400 N 0 cost 0,160,40
constraint[0]: col=0 termid=5 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=1
replace: * 6.40.3f t8 (-999,0) f 00400 N 0 cost 0,160,40
add: * 6.40.3f t8 (0,1) f 00400 N 1 cost 0,0,0
constraint[0]: col=0 termid=6 op=2 usabled=0
usage[0]: argvIdx=0 omit=0
idxNum=-999
idxStr=
orderByConsumed=0
estimatedCost=1.6e+09
estimatedRows=200
add: * 7.80.7f t7 (-999,0) f 00400 N 0 cost 0,305,76
constraint[0]: col=0 termid=6 op=2 usabled=1
usage[0]: argvIdx=1 omit=1
idxNum=0
idxStr=
orderByConsumed=0
estimatedCost=1
estimatedRows=2
replace: * 7.80.7f t7 (-999,0) f 00400 N 0 cost 0,305,76
add: * 7.80.7f t7 (0,1) f 00400 N 1 cost 0,0,10
0 0.01.00 t1 (-999,0) f 00400 N 0 cost 0,132,33
1 0.01.07 t1 (0,3) f 00400 N 2 cost 0,0,0
2 1.02.01 t2 (-999,0) f 00400 N 0 cost 0,490,120
3 1.02.3f t2 (0,f) f 00400 N 4 cost 0,0,16
4 2.04.03 t3 (0,1) f 00400 N 1 cost 0,0,10
5 3.08.07 t6 (-999,0) f 00400 N 0 cost 0,400,99
6 3.08.7f t6 (0,3) f 00400 N 2 cost 0,0,16
7 4.10.0f t4 (0,1) f 00400 N 1 cost 0,0,16
8 5.20.1f t5 (-999,0) f 00400 N 0 cost 0,500,123
9 5.20.ff t5 (0,3) f 00400 N 2 cost 0,0,16
a 6.40.3f t8 (0,1) f 00400 N 1 cost 0,0,0
b 7.80.7f t7 (0,1) f 00400 N 1 cost 0,0,10
---- begin solver. (nRowEst=0)
New 0 cost=132, 33 order=0
---- after round 0 ----
0 cost=132 nrow=33 order=0
New 02 cost=523,153 order=0
---- after round 1 ----
02 cost=523 nrow=153 order=0
New 024 cost=523,163 order=0
---- after round 2 ----
024 cost=523 nrow=163 order=0
New 0245 cost=564,262 order=0
---- after round 3 ----
0245 cost=564 nrow=262 order=0
New 02457 cost=564,278 order=0
---- after round 4 ----
02457 cost=564 nrow=278 order=0
New 024578 cost=778,401 order=0
---- after round 5 ----
024578 cost=778 nrow=401 order=0
New 024578a cost=778,401 order=0
---- after round 6 ----
024578a cost=778 nrow=401 order=0
New 024578ab cost=778,411 order=0
---- after round 7 ----
024578ab cost=778 nrow=411 order=0
---- Solution nRow=411
0 0.01.00 t1 (-999,0) f 00400 N 0 cost 0,132,33
2 1.02.01 t2 (-999,0) f 00400 N 0 cost 0,490,120
4 2.04.03 t3 (0,1) f 00400 N 1 cost 0,0,10
5 3.08.07 t6 (-999,0) f 00400 N 0 cost 0,400,99
7 4.10.0f t4 (0,1) f 00400 N 1 cost 0,0,16
8 5.20.1f t5 (-999,0) f 00400 N 0 cost 0,500,123
a 6.40.3f t8 (0,1) f 00400 N 1 cost 0,0,0
b 7.80.7f t7 (0,1) f 00400 N 1 cost 0,0,10
*** Optimizer Finished ***
=====================================
WhereTrace when NOT using virtual tables:
=====================================
*** 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
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 ***
add: * 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 ***
add: * 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 ***
add: * 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 ***
add: * 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 ***
add: * 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 ***
add: * 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 ***
add: * 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 ***
add: * 0.01.00 t1 f 00100 N 0 cost 0,216,200
add: * 1.02.01 t2 f 04000 N 1 cost 271,53,43
add: * 1.02.01 t2 f 00100 N 0 cost 0,216,180
add: * 2.04.03 t3 f 04000 N 1 cost 271,53,43
add: * 2.04.03 t3 f 00100 N 0 cost 0,216,180
add: * 3.08.07 t6 f 04000 N 1 cost 271,53,43
add: * 3.08.07 t6 f 00100 N 0 cost 0,216,180
add: * 4.10.0f t4 f 04000 N 1 cost 271,53,43
add: * 4.10.0f t4 f 00100 N 0 cost 0,216,180
add: * 5.20.1f t5 f 04000 N 1 cost 271,53,43
add: * 5.20.1f t5 f 00100 N 0 cost 0,216,180
add: * 6.40.3f t8 f 04000 N 1 cost 271,53,43
add: * 6.40.3f t8 f 00100 N 0 cost 0,216,180
add: * 7.80.7f t7 f 04000 N 1 cost 271,53,43
add: * 7.80.7f t7 f 00100 N 0 cost 0,216,180
0 0.01.00 t1 f 00100 N 0 cost 0,216,200
1 1.02.01 t2 f 04000 N 1 cost 271,53,43
2 1.02.01 t2 f 00100 N 0 cost 0,216,180
3 2.04.03 t3 f 04000 N 1 cost 271,53,43
4 2.04.03 t3 f 00100 N 0 cost 0,216,180
5 3.08.07 t6 f 04000 N 1 cost 271,53,43
6 3.08.07 t6 f 00100 N 0 cost 0,216,180
7 4.10.0f t4 f 04000 N 1 cost 271,53,43
8 4.10.0f t4 f 00100 N 0 cost 0,216,180
9 5.20.1f t5 f 04000 N 1 cost 271,53,43
a 5.20.1f t5 f 00100 N 0 cost 0,216,180
b 6.40.3f t8 f 04000 N 1 cost 271,53,43
c 6.40.3f t8 f 00100 N 0 cost 0,216,180
d 7.80.7f t7 f 04000 N 1 cost 271,53,43
e 7.80.7f t7 f 00100 N 0 cost 0,216,180
---- begin solver. (nRowEst=0)
New 0 cost=216,200 order=0
---- after round 0 ----
0 cost=216 nrow=200 order=0
New 01 cost=275,243 order=0
Skip 02 cost=416,380 order=0 vs 01 cost=275,243 order=0
---- after round 1 ----
01 cost=275 nrow=243 order=0
New 013 cost=301,286 order=0
Skip 014 cost=459,423 order=0 vs 013 cost=301,286 order=0
---- after round 2 ----
013 cost=301 nrow=286 order=0
New 0135 cost=340,329 order=0
Skip 0136 cost=502,466 order=0 vs 0135 cost=340,329 order=0
---- after round 3 ----
0135 cost=340 nrow=329 order=0
New 01357 cost=383,372 order=0
Skip 01358 cost=545,509 order=0 vs 01357 cost=383,372 order=0
---- after round 4 ----
01357 cost=383 nrow=372 order=0
New 013579 cost=426,415 order=0
Skip 01357a cost=588,552 order=0 vs 013579 cost=426,415 order=0
---- after round 5 ----
013579 cost=426 nrow=415 order=0
New 013579b cost=469,458 order=0
Skip 013579c cost=631,595 order=0 vs 013579b cost=469,458 order=0
---- after round 6 ----
013579b cost=469 nrow=458 order=0
New 013579bd cost=512,501 order=0
Skip 013579be cost=674,638 order=0 vs 013579bd cost=512,501 order=0
---- after round 7 ----
013579bd cost=512 nrow=501 order=0
---- Solution nRow=501
0 0.01.00 t1 f 00100 N 0 cost 0,216,200
1 1.02.01 t2 f 04000 N 1 cost 271,53,43
3 2.04.03 t3 f 04000 N 1 cost 271,53,43
5 3.08.07 t6 f 04000 N 1 cost 271,53,43
7 4.10.0f t4 f 04000 N 1 cost 271,53,43
9 5.20.1f t5 f 04000 N 1 cost 271,53,43
b 6.40.3f t8 f 04000 N 1 cost 271,53,43
d 7.80.7f t7 f 04000 N 1 cost 271,53,43
*** Optimizer Finished ***
_______________________________________________
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
