Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Changes to the output of EXPLAIN QUERY PLAN to make it clearer when a query uses the PRIMARY KEY index of a WITHOUT ROWID table. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
9268df305b90ac11e44b3107bbab5bec |
| User & Date: | dan 2014-05-23 17:17:06 |
Context
|
2014-05-24
| ||
| 17:15 | Remove references in comments to VDBE opcodes that no longer exist. This is a documentation change only; no changes to code. check-in: ebfb51fe user: drh tags: trunk | |
|
2014-05-23
| ||
| 17:17 | Changes to the output of EXPLAIN QUERY PLAN to make it clearer when a query uses the PRIMARY KEY index of a WITHOUT ROWID table. check-in: 9268df30 user: dan tags: trunk | |
| 12:03 | In the command-line shell, if three or more interrupt signals (control-c) are received in a row without a response from sqlite3_interrupt() then call exit(1) immediately. This allows control-C to interrupt the shell even if it is stuck in a computation or loop that does not involve the VDBE. check-in: b5cde571 user: drh tags: trunk | |
Changes
Changes to src/select.c.
4494 4495 4496 4497 4498 4499 4500 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 |
#ifndef SQLITE_OMIT_EXPLAIN
static void explainSimpleCount(
Parse *pParse, /* Parse context */
Table *pTab, /* Table being queried */
Index *pIdx /* Index used to optimize scan, or NULL */
){
if( pParse->explain==2 ){
char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s",
pTab->zName,
pIdx ? " USING COVERING INDEX " : "",
pIdx ? pIdx->zName : ""
);
sqlite3VdbeAddOp4(
pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
);
}
}
#else
|
> | | | |
4494 4495 4496 4497 4498 4499 4500 4501 4502 4503 4504 4505 4506 4507 4508 4509 4510 4511 4512 |
#ifndef SQLITE_OMIT_EXPLAIN
static void explainSimpleCount(
Parse *pParse, /* Parse context */
Table *pTab, /* Table being queried */
Index *pIdx /* Index used to optimize scan, or NULL */
){
if( pParse->explain==2 ){
int bCover = (pIdx!=0 && (HasRowid(pTab) || pIdx->autoIndex!=2));
char *zEqp = sqlite3MPrintf(pParse->db, "SCAN TABLE %s%s%s",
pTab->zName,
bCover ? " USING COVERING INDEX " : "",
bCover ? pIdx->zName : ""
);
sqlite3VdbeAddOp4(
pParse->pVdbe, OP_Explain, pParse->iSelectId, 0, 0, zEqp, P4_DYNAMIC
);
}
}
#else
|
Changes to src/where.c.
2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 |
if( pItem->zAlias ){
zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
}
if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0
&& ALWAYS(pLoop->u.btree.pIndex!=0)
){
char *zWhere = explainIndexRange(db, pLoop, pItem->pTab);
zMsg = sqlite3MAppendf(db, zMsg,
((flags & WHERE_AUTO_INDEX) ?
"%s USING AUTOMATIC %sINDEX%.0s%s" :
"%s USING %sINDEX %s%s"),
zMsg, ((flags & WHERE_IDX_ONLY) ? "COVERING " : ""),
pLoop->u.btree.pIndex->zName, zWhere);
sqlite3DbFree(db, zWhere);
}else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){
zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){
zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
}else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
|
> > < > > > | | < | < > > > > > |
2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 |
if( pItem->zAlias ){
zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
}
if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0
&& ALWAYS(pLoop->u.btree.pIndex!=0)
){
const char *zFmt;
Index *pIdx = pLoop->u.btree.pIndex;
char *zWhere = explainIndexRange(db, pLoop, pItem->pTab);
assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );
if( !HasRowid(pItem->pTab) && pIdx->autoIndex==2 ){
zFmt = zWhere ? "%s USING PRIMARY KEY%.0s%s" : "%s%.0s%s";
}else if( flags & WHERE_AUTO_INDEX ){
zFmt = "%s USING AUTOMATIC COVERING INDEX%.0s%s";
}else if( flags & WHERE_IDX_ONLY ){
zFmt = "%s USING COVERING INDEX %s%s";
}else{
zFmt = "%s USING INDEX %s%s";
}
zMsg = sqlite3MAppendf(db, zMsg, zFmt, zMsg, pIdx->zName, zWhere);
sqlite3DbFree(db, zWhere);
}else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){
zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);
if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){
zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
}else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
|
Changes to test/eqp.test.
593 594 595 596 597 598 599 600 601 602 |
det 7.4 "SELECT count(*) FROM t1" {
0 0 0 {SCAN TABLE t1}
}
det 7.5 "SELECT count(*) FROM t2" {
0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
}
finish_test
|
> > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 |
det 7.4 "SELECT count(*) FROM t1" {
0 0 0 {SCAN TABLE t1}
}
det 7.5 "SELECT count(*) FROM t2" {
0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1}
}
#-------------------------------------------------------------------------
# The following tests - eqp-8.* - test that queries that use the OP_Count
# optimization return something sensible with EQP.
#
drop_all_tables
do_execsql_test 8.0 {
CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
CREATE TABLE t2(a, b, c);
}
det 8.1.1 "SELECT * FROM t2" {
0 0 0 {SCAN TABLE t2}
}
det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" {
0 0 0 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
}
det 8.1.3 "SELECT count(*) FROM t2" {
0 0 0 {SCAN TABLE t2}
}
det 8.2.1 "SELECT * FROM t1" {
0 0 0 {SCAN TABLE t1}
}
det 8.2.2 "SELECT * FROM t1 WHERE b=?" {
0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=?)}
}
det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" {
0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=? AND c=?)}
}
det 8.2.4 "SELECT count(*) FROM t1" {
0 0 0 {SCAN TABLE t1}
}
finish_test
|
Changes to test/skipscan1.test.
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
do_execsql_test skipscan1-3.2 {
SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
} {abc 345 7 8 | def 345 9 10 |}
do_execsql_test skipscan1-3.2eqp {
EXPLAIN QUERY PLAN
SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
} {/* INDEX sqlite_autoindex_t3_1 (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-3.2sort {
EXPLAIN QUERY PLAN
SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
} {~/*ORDER BY*/}
# Ticket 520070ec7fbaac: Array overrun in the skip-scan optimization
# 2013-12-22
|
| |
177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
do_execsql_test skipscan1-3.2 {
SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
} {abc 345 7 8 | def 345 9 10 |}
do_execsql_test skipscan1-3.2eqp {
EXPLAIN QUERY PLAN
SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
} {/* PRIMARY KEY (ANY(a) AND b=?)*/}
do_execsql_test skipscan1-3.2sort {
EXPLAIN QUERY PLAN
SELECT a,b,c,d,'|' FROM t3 WHERE b=345 ORDER BY a;
} {~/*ORDER BY*/}
# Ticket 520070ec7fbaac: Array overrun in the skip-scan optimization
# 2013-12-22
|
Changes to test/skipscan2.test.
195 196 197 198 199 200 201 202 203 204 205 |
for {set i 0} {$i < 1000} {incr i} {
execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }
}
execsql { ANALYZE }
} {}
do_eqp_test skipscan2-3.3eqp {
SELECT * FROM t3 WHERE b=42;
} {0 0 0 {SEARCH TABLE t3 USING COVERING INDEX sqlite_autoindex_t3_1 (ANY(a) AND b=?)}}
finish_test
|
| |
195 196 197 198 199 200 201 202 203 204 205 |
for {set i 0} {$i < 1000} {incr i} {
execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }
}
execsql { ANALYZE }
} {}
do_eqp_test skipscan2-3.3eqp {
SELECT * FROM t3 WHERE b=42;
} {0 0 0 {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}}
finish_test
|