Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Ensure that automatic indexes are only created in scenarios where they may be used more than once. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
27c65d4d9c58bfc4ea8f9337fa150904 |
User & Date: | dan 2011-07-02 15:32:57 |
References
2011-07-02
| ||
15:42 | Cherrypick change [27c65d4d9c] into the 3.7.2 branch. (check-in: 9bbcd8c0 user: dan tags: branch-3.7.2) | |
Context
2011-07-02
| ||
19:12 | Add a testcase macro to ensure testing a boundary case in DISTINCT processing. (check-in: ff9fc722 user: drh tags: trunk) | |
15:32 | Ensure that automatic indexes are only created in scenarios where they may be used more than once. (check-in: 27c65d4d user: dan tags: trunk) | |
09:46 | Merge experimental changes improving optimization of DISTINCT queries with the trunk. (check-in: 45e581bf user: dan tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 | double nTableRow; /* Rows in the input table */ double logN; /* log(nTableRow) */ double costTempIdx; /* per-query cost of the transient index */ WhereTerm *pTerm; /* A single term of the WHERE clause */ WhereTerm *pWCEnd; /* End of pWC->a[] */ Table *pTable; /* Table tht might be indexed */ if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){ /* Automatic indices are disabled at run-time */ return; } if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){ /* We already have some kind of index in use for this query. */ return; | > > > > | 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 | double nTableRow; /* Rows in the input table */ double logN; /* log(nTableRow) */ double costTempIdx; /* per-query cost of the transient index */ WhereTerm *pTerm; /* A single term of the WHERE clause */ WhereTerm *pWCEnd; /* End of pWC->a[] */ Table *pTable; /* Table tht might be indexed */ if( pParse->nQueryLoop<=(double)1 ){ /* There is no point in building an automatic index for a single scan */ return; } if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){ /* Automatic indices are disabled at run-time */ return; } if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){ /* We already have some kind of index in use for this query. */ return; |
︙ | ︙ |
Changes to test/autoindex1.test.
︙ | ︙ | |||
243 244 245 246 247 248 249 250 251 | 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)} } finish_test | > > > > > > > > > > | 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 | 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)} } do_execsql_test autoindex1-700 { CREATE TABLE t5(a, b, c); EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c; } { 0 0 0 {SCAN TABLE t5 (~100000 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test |