Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Allow an automatic index on a WHERE constraint where the RHS is does not reference another table as long as the RHS is not a constant. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | autoindex-improvements |
| Files: | files | file ages | folders |
| SHA1: |
58acc2a8b38c5f13175c191697b4d2b6 |
| User & Date: | drh 2014-06-17 09:00:54 |
Context
|
2014-06-17
| ||
| 09:52 | Simplification to the logic that prevents automatic indexes from being formed on constant expressions. check-in: 36d67e1d user: drh tags: autoindex-improvements | |
| 09:00 | Allow an automatic index on a WHERE constraint where the RHS is does not reference another table as long as the RHS is not a constant. check-in: 58acc2a8 user: drh tags: autoindex-improvements | |
| 02:46 | Do not attempt to create an automatic index on a constant constraint, as doing so is pointless. check-in: d6883e96 user: drh tags: autoindex-improvements | |
Changes
Changes to ext/rtree/rtree6.test.
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
do_eqp_test rtree6.2.3 {
SELECT * FROM t1,t2 WHERE k=ii
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_eqp_test rtree6.2.4 {
SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.5 {
SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
|
| | > > > > > > |
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
do_eqp_test rtree6.2.3 {
SELECT * FROM t1,t2 WHERE k=ii
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_eqp_test rtree6.2.4.1 {
SELECT * FROM t1,t2 WHERE v=+ii and x1<10 and x2>10
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (v=?)}
}
do_eqp_test rtree6.2.4.2 {
SELECT * FROM t1,t2 WHERE v=10 and x1<10 and x2>10
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:C0E1}
0 1 1 {SCAN TABLE t2}
}
do_eqp_test rtree6.2.5 {
SELECT * FROM t1,t2 WHERE k=ii AND x1<v
} {
0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 2:}
0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
|
Changes to src/where.c.
4525 4526 4527 4528 4529 4530 4531 4532 4533 4534 4535 4536 4537 4538 4539 |
&& !pSrc->isRecursive
){
/* Generate auto-index WhereLoops */
WhereTerm *pTerm;
WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
if( pTerm->prereqRight & pNew->maskSelf ) continue;
if( pTerm->prereqRight==0 ) continue;
if( termCanDriveIndex(pTerm, pSrc, 0) ){
pNew->u.btree.nEq = 1;
pNew->u.btree.nSkip = 0;
pNew->u.btree.pIndex = 0;
pNew->nLTerm = 1;
pNew->aLTerm[0] = pTerm;
/* TUNING: One-time cost for computing the automatic index is
|
| > > > |
4525 4526 4527 4528 4529 4530 4531 4532 4533 4534 4535 4536 4537 4538 4539 4540 4541 4542 |
&& !pSrc->isRecursive
){
/* Generate auto-index WhereLoops */
WhereTerm *pTerm;
WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
if( pTerm->prereqRight & pNew->maskSelf ) continue;
if( pTerm->prereqRight==0
&& sqlite3ExprIsConstant(pTerm->pExpr->pRight) ){
continue;
}
if( termCanDriveIndex(pTerm, pSrc, 0) ){
pNew->u.btree.nEq = 1;
pNew->u.btree.nSkip = 0;
pNew->u.btree.pIndex = 0;
pNew->nLTerm = 1;
pNew->aLTerm[0] = pTerm;
/* TUNING: One-time cost for computing the automatic index is
|
Changes to test/tpch01.test.
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
...
181
182
183
184
185
186
187
|
o_year
order by
o_year;}]
set ::eqpres
} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
do_test tpch01-1.1b {
set ::eqpres
} {/.* customer .* nation AS n1 .* nation AS n2 .*/}
do_eqp_test tpch01-1.2 {
select
c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal, n_name, c_address, c_phone, c_comment
from
customer, orders, lineitem, nation
................................................................................
and o_orderdate >= '1994-08-01' and o_orderdate < date('1994-08-01', '+3 month')
and l_returnflag = 'R' and c_nationkey = n_nationkey
group by
c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
order by
revenue desc;
} {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
|
|
>
>
>
>
>
|
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
...
184
185
186
187
188
189
190
191
192
|
o_year
order by
o_year;}]
set ::eqpres
} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
do_test tpch01-1.1b {
set ::eqpres
} {/.* customer .* nation AS n1 .*/}
do_test tpch01-1.1c {
set ::eqpres
} {/.* supplier .* nation AS n2 .*/}
do_eqp_test tpch01-1.2 {
select
c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal, n_name, c_address, c_phone, c_comment
from
customer, orders, lineitem, nation
................................................................................
and o_orderdate >= '1994-08-01' and o_orderdate < date('1994-08-01', '+3 month')
and l_returnflag = 'R' and c_nationkey = n_nationkey
group by
c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
order by
revenue desc;
} {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
finish_test
|