SQLite4
Check-in [faac50a266]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix some issues with assigning costs to loops in where.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: faac50a2668470bc866c6b4ee85b482bbae455ae
User & Date: dan 2013-07-26 19:13:03
Context
2013-07-26
20:04
Update where.c with patches from sqlite3. src4 where.c is now based on sqlite3 artifact 1a26c37b7b. check-in: 3f4a1c7648 user: dan tags: trunk
19:13
Fix some issues with assigning costs to loops in where.c. check-in: faac50a266 user: dan tags: trunk
16:59
Fix minor errors in the key encoder. check-in: 0a923f20d2 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/where.c.

436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
....
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221

3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233

3234
3235
3236
3237
3238
3239
3240
....
3359
3360
3361
3362
3363
3364
3365

3366
3367
3368
3369
3370
3371
3372
....
3466
3467
3468
3469
3470
3471
3472


3473
3474
3475
3476
3477
3478
3479
3480
....
4495
4496
4497
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
....
4678
4679
4680
4681
4682
4683
4684
4685


4686
4687
4688
4689
4690
4691
4692
....
5153
5154
5155
5156
5157
5158
5159

5160
5161
5162


5163
5164
5165
5166
5167
5168
5169
5170
....
6168
6169
6170
6171
6172
6173
6174
6175
6176
6177
6178
6179
6180
6181
6182
#define WHERE_COLUMN_IN    0x00000004  /* x IN (...) */
#define WHERE_COLUMN_NULL  0x00000008  /* x IS NULL */
#define WHERE_CONSTRAINT   0x0000000f  /* Any of the WHERE_COLUMN_xxx values */
#define WHERE_TOP_LIMIT    0x00000010  /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT    0x00000020  /* x>EXPR or x>=EXPR constraint */
#define WHERE_BOTH_LIMIT   0x00000030  /* Both x>EXPR and x<EXPR */
#define WHERE_IDX_ONLY     0x00000040  /* Use index only - omit table */
#define WHERE_IPK          0x00000100  /* x is the INTEGER PRIMARY KEY */
#define WHERE_INDEXED      0x00000200  /* WhereLoop.u.btree.pIndex is valid */
#define WHERE_VIRTUALTABLE 0x00000400  /* WhereLoop.u.vtab is valid */
#define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
#define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
#define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
#define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */

................................................................................
    }else{
      zMsg = sqlite4MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName);
    }

    if( pItem->zAlias ){
      zMsg = sqlite4MAppendf(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 = sqlite4MAppendf(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);
      sqlite4DbFree(db, zWhere);

    }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){
      zMsg = sqlite4MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);

      if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
      }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
      }else if( ALWAYS(flags&WHERE_TOP_LIMIT) ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid<?)", zMsg);
      }

    }
#ifndef SQLITE4_OMIT_VIRTUALTABLE
    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
      zMsg = sqlite4MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
                  pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
    }
#endif
................................................................................
    pLevel->p1 = iCur;
    pLevel->p2 = sqlite4VdbeCurrentAddr(v);
    sqlite4ReleaseTempRange(pParse, iReg, nConstraint+2);
    sqlite4ExprCachePop(pParse, 1);
  }else
#endif /* SQLITE4_OMIT_VIRTUALTABLE */


  if( (pLoop->wsFlags & WHERE_IPK)!=0
   && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0
  ){
    assert( 0 );

    /* Case 2:  We can directly reference a single row using an
    **          equality comparison against the ROWID field.  Or
................................................................................
    if( testOp!=OP_Noop ){
      iRowidReg = iReleaseReg = sqlite4GetTempReg(pParse);
      sqlite4VdbeAddOp2(v, OP_Rowid, iCur, iRowidReg);
      sqlite4ExprCacheStore(pParse, iCur, -1, iRowidReg);
      sqlite4VdbeAddOp3(v, testOp, memEndValue, addrBrk, iRowidReg);
      sqlite4VdbeChangeP5(v, SQLITE4_AFF_NUMERIC | SQLITE4_JUMPIFNULL);
    }


  }else if( pLoop->wsFlags & WHERE_INDEXED ){
    /* Case 4: A scan using an index.
    **
    **         The WHERE clause may contain zero or more equality 
    **         terms ("==" or "IN" operators) that refer to the N
    **         left-most columns of the index. It may also contain
    **         inequality constraints (>, <, >= or <=) on the indexed
    **         column that immediately follows the N equalities. Only 
................................................................................
      }else if( (pTerm->eOperator & WO_IN)
             &&  !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)  ){
        rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut);
      }
      if( rc==SQLITE4_OK ) pNew->nOut = whereCost(nOut);
    }
#endif
    if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0 ){
      /* Each row involves a step of the index, then a binary search of
      ** the main table */
      pNew->rRun =  whereCostAdd(pNew->rRun, rLogSize>27 ? rLogSize-17 : 10);
    }
    /* Step cost for each output row */
    pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
    /* TBD: Adjust nOut for additional constraints */
................................................................................

    pNew->u.btree.nEq = 0;
    pNew->nLTerm = 0;
    pNew->rSetup = 0;
    pNew->prereq = mExtra;
    pNew->nOut = rSize;
    pNew->u.btree.pIndex = pProbe;
    pNew->wsFlags = bCover ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;



    b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
    /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */
    assert( (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 );
    pNew->iSortIdx = b ? iSortIdx : 0;

    if( pProbe==pPk || b || (bCover
................................................................................
        if( sqlite4_stricmp(z1, z2)!=0 ) continue;
      }
      obSat |= MASKBIT(i);
    }

    if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
      Index *pPk = 0;

      if( pLoop->wsFlags & WHERE_IPK ){
        pIndex = 0;
        nColumn = 0;


      }else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
        return 0;
      }else{
        isOrderDistinct = pIndex->onError!=OE_None;
        pPk = sqlite4FindPrimaryKey(pIndex->pTable, 0);
        nColumn = idxColumnCount(pIndex, pPk);
      }

................................................................................
     && pTab->pSelect==0
     && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0
    ){
      int ws = pLoop->wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite4VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & WHERE_INDEXED)!=0 && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0 ){
        if( pLevel->iIdxCur!=pTabItem->iCursor ){
          sqlite4VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
        }
      }
    }

    /* If this scan uses an index, make VDBE code substitutions to read data







|







 







|










>












>







 







>







 







>
>
|







 







|







 







|
>
>







 







>



>
>
|







 







|







436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
....
3204
3205
3206
3207
3208
3209
3210
3211
3212
3213
3214
3215
3216
3217
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
3239
3240
3241
3242
....
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
....
3469
3470
3471
3472
3473
3474
3475
3476
3477
3478
3479
3480
3481
3482
3483
3484
3485
....
4500
4501
4502
4503
4504
4505
4506
4507
4508
4509
4510
4511
4512
4513
4514
....
4683
4684
4685
4686
4687
4688
4689
4690
4691
4692
4693
4694
4695
4696
4697
4698
4699
....
5160
5161
5162
5163
5164
5165
5166
5167
5168
5169
5170
5171
5172
5173
5174
5175
5176
5177
5178
5179
5180
....
6178
6179
6180
6181
6182
6183
6184
6185
6186
6187
6188
6189
6190
6191
6192
#define WHERE_COLUMN_IN    0x00000004  /* x IN (...) */
#define WHERE_COLUMN_NULL  0x00000008  /* x IS NULL */
#define WHERE_CONSTRAINT   0x0000000f  /* Any of the WHERE_COLUMN_xxx values */
#define WHERE_TOP_LIMIT    0x00000010  /* x<EXPR or x<=EXPR constraint */
#define WHERE_BTM_LIMIT    0x00000020  /* x>EXPR or x>=EXPR constraint */
#define WHERE_BOTH_LIMIT   0x00000030  /* Both x>EXPR and x<EXPR */
#define WHERE_IDX_ONLY     0x00000040  /* Use index only - omit table */
#define WHERE_PRIMARY_KEY  0x00000100  /* Index is the PK index */
#define WHERE_INDEXED      0x00000200  /* WhereLoop.u.btree.pIndex is valid */
#define WHERE_VIRTUALTABLE 0x00000400  /* WhereLoop.u.vtab is valid */
#define WHERE_IN_ABLE      0x00000800  /* Able to support an IN operator */
#define WHERE_ONEROW       0x00001000  /* Selects no more than one row */
#define WHERE_MULTI_OR     0x00002000  /* OR using multiple indices */
#define WHERE_AUTO_INDEX   0x00004000  /* Uses an ephemeral index */

................................................................................
    }else{
      zMsg = sqlite4MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName);
    }

    if( pItem->zAlias ){
      zMsg = sqlite4MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias);
    }
    if( (flags & WHERE_VIRTUALTABLE)==0
     && ALWAYS(pLoop->u.btree.pIndex!=0)
    ){
      char *zWhere = explainIndexRange(db, pLoop, pItem->pTab);
      zMsg = sqlite4MAppendf(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);
      sqlite4DbFree(db, zWhere);
#if 0
    }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){
      zMsg = sqlite4MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg);

      if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid=?)", zMsg);
      }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg);
      }else if( flags&WHERE_BTM_LIMIT ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
      }else if( ALWAYS(flags&WHERE_TOP_LIMIT) ){
        zMsg = sqlite4MAppendf(db, zMsg, "%s (rowid<?)", zMsg);
      }
#endif
    }
#ifndef SQLITE4_OMIT_VIRTUALTABLE
    else if( (flags & WHERE_VIRTUALTABLE)!=0 ){
      zMsg = sqlite4MAppendf(db, zMsg, "%s VIRTUAL TABLE INDEX %d:%s", zMsg,
                  pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);
    }
#endif
................................................................................
    pLevel->p1 = iCur;
    pLevel->p2 = sqlite4VdbeCurrentAddr(v);
    sqlite4ReleaseTempRange(pParse, iReg, nConstraint+2);
    sqlite4ExprCachePop(pParse, 1);
  }else
#endif /* SQLITE4_OMIT_VIRTUALTABLE */

#if 0
  if( (pLoop->wsFlags & WHERE_IPK)!=0
   && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0
  ){
    assert( 0 );

    /* Case 2:  We can directly reference a single row using an
    **          equality comparison against the ROWID field.  Or
................................................................................
    if( testOp!=OP_Noop ){
      iRowidReg = iReleaseReg = sqlite4GetTempReg(pParse);
      sqlite4VdbeAddOp2(v, OP_Rowid, iCur, iRowidReg);
      sqlite4ExprCacheStore(pParse, iCur, -1, iRowidReg);
      sqlite4VdbeAddOp3(v, testOp, memEndValue, addrBrk, iRowidReg);
      sqlite4VdbeChangeP5(v, SQLITE4_AFF_NUMERIC | SQLITE4_JUMPIFNULL);
    }
  }else 
#endif
  if( pLoop->wsFlags & WHERE_INDEXED ){
    /* Case 4: A scan using an index.
    **
    **         The WHERE clause may contain zero or more equality 
    **         terms ("==" or "IN" operators) that refer to the N
    **         left-most columns of the index. It may also contain
    **         inequality constraints (>, <, >= or <=) on the indexed
    **         column that immediately follows the N equalities. Only 
................................................................................
      }else if( (pTerm->eOperator & WO_IN)
             &&  !ExprHasProperty(pTerm->pExpr, EP_xIsSelect)  ){
        rc = whereInScanEst(pParse, pProbe, pTerm->pExpr->x.pList, &nOut);
      }
      if( rc==SQLITE4_OK ) pNew->nOut = whereCost(nOut);
    }
#endif
    if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_PRIMARY_KEY))==0 ){
      /* Each row involves a step of the index, then a binary search of
      ** the main table */
      pNew->rRun =  whereCostAdd(pNew->rRun, rLogSize>27 ? rLogSize-17 : 10);
    }
    /* Step cost for each output row */
    pNew->rRun = whereCostAdd(pNew->rRun, pNew->nOut);
    /* TBD: Adjust nOut for additional constraints */
................................................................................

    pNew->u.btree.nEq = 0;
    pNew->nLTerm = 0;
    pNew->rSetup = 0;
    pNew->prereq = mExtra;
    pNew->nOut = rSize;
    pNew->u.btree.pIndex = pProbe;
    pNew->wsFlags = WHERE_INDEXED;
    pNew->wsFlags |= (bCover ? WHERE_IDX_ONLY : 0); 
    pNew->wsFlags |= (pProbe==pPk ? WHERE_PRIMARY_KEY : 0);

    b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
    /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */
    assert( (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 );
    pNew->iSortIdx = b ? iSortIdx : 0;

    if( pProbe==pPk || b || (bCover
................................................................................
        if( sqlite4_stricmp(z1, z2)!=0 ) continue;
      }
      obSat |= MASKBIT(i);
    }

    if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
      Index *pPk = 0;
#if 0
      if( pLoop->wsFlags & WHERE_IPK ){
        pIndex = 0;
        nColumn = 0;
      }else 
#endif
      if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){
        return 0;
      }else{
        isOrderDistinct = pIndex->onError!=OE_None;
        pPk = sqlite4FindPrimaryKey(pIndex->pTable, 0);
        nColumn = idxColumnCount(pIndex, pPk);
      }

................................................................................
     && pTab->pSelect==0
     && (pWInfo->wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0
    ){
      int ws = pLoop->wsFlags;
      if( !pWInfo->okOnePass && (ws & WHERE_IDX_ONLY)==0 ){
        sqlite4VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
      }
      if( (ws & WHERE_INDEXED)!=0 && (ws & WHERE_AUTO_INDEX)==0 ){
        if( pLevel->iIdxCur!=pTabItem->iCursor ){
          sqlite4VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
        }
      }
    }

    /* If this scan uses an index, make VDBE code substitutions to read data

Changes to test/autoindex1.test.

165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
...
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
...
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
...
384
385
386
387
388
389
390


391
392
393
394
395
396
397
398
399
400
401
402
403
404
# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000');
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  0 0 0 {SEARCH TABLE t501 USING INDEX t501 (a=?)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
................................................................................
}
do_execsql_test autoindex1-502 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} 
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502}
}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field
# of ephermeral tables was not being initialized correctly and so no
# automatic index was being created for the emphemeral table when it was
................................................................................
           WHERE prev.flock_no = later.flock_no
           AND later.owner_change_date > prev.owner_change_date
           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
       ) y ON x.sheep_no = y.sheep_no
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s} 
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?)} 
  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<?)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)}
}


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} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# The following checks a performance issue reported on the sqlite-dev
# mailing list on 2013-01-10
#
do_execsql_test autoindex1-800 {
................................................................................
     VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
            '1600 1600 1600');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
  INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');


  INSERT INTO sqlite_stat1
     VALUES('data','data_mimetype_data1_index','9819 2455 3');
  INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
  INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1');
  DROP TABLE IF EXISTS sqlite_stat3;
  ANALYZE sqlite_master;
  
  EXPLAIN QUERY PLAN
  SELECT * FROM 
        data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
             JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
             JOIN accounts ON (raw_contacts.account_id=accounts._id)
   WHERE mimetype_id=10 AND data14 IS NOT NULL;
} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/}







|
|







 







|

|







 







|
|
|
|









|







 







>
>



|


<







165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
...
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
...
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
...
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398

399
400
401
402
403
404
405
# Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
# Make sure automatic indices are not created for the RHS of an IN expression
# that is not a correlated subquery.
#
do_execsql_test autoindex1-500 {
  CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
  CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501','t501','1000000');
  INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502','t502','1000');
  ANALYZE sqlite_master;
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
  0 0 0 {SEARCH TABLE t501 USING INDEX t501 (a=?)} 
  0 0 0 {EXECUTE LIST SUBQUERY 1} 
................................................................................
}
do_execsql_test autoindex1-502 {
  EXPLAIN QUERY PLAN
  SELECT b FROM t501
   WHERE t501.a=123
     AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
  0 0 0 {SEARCH TABLE t501 USING INDEX t501 (a=?)}
  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
  1 0 0 {SCAN TABLE t502 USING INDEX t502}
}


# The following code checks a performance regression reported on the
# mailing list on 2010-10-19.  The problem is that the nRowEst field
# of ephermeral tables was not being initialized correctly and so no
# automatic index was being created for the emphemeral table when it was
................................................................................
           WHERE prev.flock_no = later.flock_no
           AND later.owner_change_date > prev.owner_change_date
           AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
       ) y ON x.sheep_no = y.sheep_no
   WHERE y.sheep_no IS NULL
   ORDER BY x.registering_flock;
} {
  1 0 0 {SCAN TABLE sheep AS s USING INDEX sheep}
  1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_flock_owner_unique2 (flock_no=? AND owner_change_date<?)} 
  1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
  2 0 0 {SEARCH TABLE flock_owner AS later USING INDEX sqlite_flock_owner_unique2 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} 
  0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} 
  0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)}
}


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 USING INDEX t5} 
  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}

# The following checks a performance issue reported on the sqlite-dev
# mailing list on 2013-01-10
#
do_execsql_test autoindex1-800 {
................................................................................
     VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contacts_source_id_account_id_index',
            '1600 1600 1600');
  INSERT INTO sqlite_stat1
     VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1');
  INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1');
  INSERT INTO sqlite_stat1 VALUES('mimetypes','mimetypes','12');
  INSERT INTO sqlite_stat1 VALUES('raw_contacts','raw_contacts','1600');
  INSERT INTO sqlite_stat1
     VALUES('data','data_mimetype_data1_index','9819 2455 3');
  INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7');
  INSERT INTO sqlite_stat1 VALUES('accounts','accounts','1');
  DROP TABLE IF EXISTS sqlite_stat3;
  ANALYZE sqlite_master;

  EXPLAIN QUERY PLAN
  SELECT * FROM 
        data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) 
             JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) 
             JOIN accounts ON (raw_contacts.account_id=accounts._id)
   WHERE mimetype_id=10 AND data14 IS NOT NULL;
} {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/}