/ Check-in [4ab3df25]
Login

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

Overview
Comment:Have the schemalint output distinguish between existing and recommended indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: 4ab3df25f1fee7c8fea19d0c64b3e0e4d3b9c3cf
User & Date: dan 2016-02-18 19:10:02
Context
2016-02-19
07:53
Fix a couple of bugs in the schemalint code. check-in: 02fbf699 user: dan tags: schemalint
2016-02-18
19:10
Have the schemalint output distinguish between existing and recommended indexes. check-in: 4ab3df25 user: dan tags: schemalint
2016-02-17
20:06
Schemalint changes: Avoid creating candidate indexes if a compatible index exists. Do not quote identifiers that do not require it. check-in: cf0f7eeb user: dan tags: schemalint
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/shell_indexes.c.

    94     94     IdxScan *pNextScan;             /* Next IdxScan object for same query */
    95     95   };
    96     96   
    97     97   /*
    98     98   ** Context object passed to idxWhereInfo()
    99     99   */
   100    100   struct IdxContext {
          101  +  char **pzErrmsg;
   101    102     IdxWhere *pCurrent;             /* Current where clause */
   102    103     int rc;                         /* Error code (if error has occurred) */
   103    104     IdxScan *pScan;                 /* List of scan objects */
   104    105     sqlite3 *dbm;                   /* In-memory db for this analysis */
   105    106     sqlite3 *db;                    /* User database under analysis */
   106    107     sqlite3_stmt *pInsertMask;      /* To write to aux.depmask */
          108  +  i64 iIdxRowid;                  /* Rowid of first index created */
   107    109   };
   108    110   
   109    111   /*
   110    112   ** Data regarding a database table. Extracted from "PRAGMA table_info"
   111    113   */
   112    114   struct IdxColumn {
   113    115     char *zName;
................................................................................
   583    585     if( rc==SQLITE_OK ) rc = rc2;
   584    586   
   585    587     *pRc = rc;
   586    588     return 0;
   587    589   }
   588    590   
   589    591   static int idxCreateFromCons(
   590         -  sqlite3 *dbm,
          592  +  IdxContext *pCtx,
   591    593     IdxScan *pScan,
   592    594     IdxConstraint *pEq, 
   593    595     IdxConstraint *pTail
   594    596   ){
          597  +  sqlite3 *dbm = pCtx->dbm;
   595    598     int rc = SQLITE_OK;
   596    599     if( (pEq || pTail) && 0==idxFindCompatible(&rc, dbm, pScan, pEq, pTail) ){
   597    600       IdxTable *pTab = pScan->pTable;
   598    601       char *zCols = 0;
   599    602       char *zIdx = 0;
   600    603       IdxConstraint *pCons;
   601    604       int h = 0;
................................................................................
   604    607       for(pCons=pEq; pCons; pCons=pCons->pLink){
   605    608         zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
   606    609       }
   607    610       for(pCons=pTail; pCons; pCons=pCons->pLink){
   608    611         zCols = idxAppendColDefn(&rc, zCols, pTab, pCons);
   609    612       }
   610    613   
   611         -    /* Hash the list of columns to come up with a name for the index */
   612    614       if( rc==SQLITE_OK ){
          615  +      /* Hash the list of columns to come up with a name for the index */
   613    616         int i;
   614    617         for(i=0; zCols[i]; i++){
   615    618           h += ((h<<3) + zCols[i]);
   616    619         }
   617    620   
   618    621         if( idxIdentifierRequiresQuotes(pScan->zTable) ){
   619    622           zFmt = "CREATE INDEX '%q_idx_%08x' ON %Q(%s)";
................................................................................
   626    629         }else{
   627    630           rc = sqlite3_exec(dbm, zIdx, 0, 0, 0);
   628    631   #if 0
   629    632           printf("/* %s */\n", zIdx);
   630    633   #endif
   631    634         }
   632    635       }
          636  +    if( rc==SQLITE_OK && pCtx->iIdxRowid==0 ){
          637  +      int rc2;
          638  +      sqlite3_stmt *pLast = 0;
          639  +      rc = idxPrepareStmt(dbm, &pLast, pCtx->pzErrmsg, 
          640  +          "SELECT max(rowid) FROM sqlite_master"
          641  +      );
          642  +      if( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pLast) ){
          643  +        pCtx->iIdxRowid = sqlite3_column_int64(pLast, 0);
          644  +      }
          645  +      rc2 = sqlite3_finalize(pLast);
          646  +      if( rc==SQLITE_OK ) rc = rc2;
          647  +    }
   633    648   
   634    649       sqlite3_free(zIdx);
   635    650       sqlite3_free(zCols);
   636    651     }
   637    652     return rc;
   638    653   }
   639    654   
   640    655   static int idxCreateFromWhere(
   641         -    sqlite3*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
          656  +    IdxContext*, i64, IdxScan*, IdxWhere*, IdxConstraint*, IdxConstraint*
   642    657   );
   643    658   
   644    659   static int idxCreateForeachOr(
   645         -  sqlite3 *dbm, 
          660  +  IdxContext *pCtx, 
   646    661     i64 mask,                       /* Consider only these constraints */
   647    662     IdxScan *pScan,                 /* Create indexes for this scan */
   648    663     IdxWhere *pWhere,               /* Read constraints from here */
   649    664     IdxConstraint *pEq,             /* == constraints for inclusion */
   650    665     IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
   651    666   ){
   652    667     int rc = SQLITE_OK;
   653    668     IdxWhere *p1;
   654    669     IdxWhere *p2;
   655    670     for(p1=pWhere->pOr; p1 && rc==SQLITE_OK; p1=p1->pNextOr){
   656         -    rc = idxCreateFromWhere(dbm, mask, pScan, p1, pEq, pTail);
          671  +    rc = idxCreateFromWhere(pCtx, mask, pScan, p1, pEq, pTail);
   657    672       for(p2=p1->pSibling; p2 && rc==SQLITE_OK; p2=p2->pSibling){
   658         -      rc = idxCreateFromWhere(dbm, mask, pScan, p2, pEq, pTail);
          673  +      rc = idxCreateFromWhere(pCtx, mask, pScan, p2, pEq, pTail);
   659    674       }
   660    675     }
   661    676     return rc;
   662    677   }
   663    678   
   664    679   static int idxCreateFromWhere(
   665         -  sqlite3 *dbm, 
          680  +  IdxContext *pCtx, 
   666    681     i64 mask,                       /* Consider only these constraints */
   667    682     IdxScan *pScan,                 /* Create indexes for this scan */
   668    683     IdxWhere *pWhere,               /* Read constraints from here */
   669    684     IdxConstraint *pEq,             /* == constraints for inclusion */
   670    685     IdxConstraint *pTail            /* range/ORDER BY constraints for inclusion */
   671    686   ){
          687  +  sqlite3 *dbm = pCtx->dbm;
   672    688     IdxConstraint *p1 = pEq;
   673    689     IdxConstraint *pCon;
   674    690     int rc;
   675    691   
   676    692     /* Gather up all the == constraints that match the mask. */
   677    693     for(pCon=pWhere->pEq; pCon; pCon=pCon->pNext){
   678    694       if( (mask & pCon->depmask)==pCon->depmask ){
................................................................................
   679    695         pCon->pLink = p1;
   680    696         p1 = pCon;
   681    697       }
   682    698     }
   683    699   
   684    700     /* Create an index using the == constraints collected above. And the
   685    701     ** range constraint/ORDER BY terms passed in by the caller, if any. */
   686         -  rc = idxCreateFromCons(dbm, pScan, p1, pTail);
          702  +  rc = idxCreateFromCons(pCtx, pScan, p1, pTail);
   687    703     if( rc==SQLITE_OK ){
   688         -    rc = idxCreateForeachOr(dbm, mask, pScan, pWhere, p1, pTail);
          704  +    rc = idxCreateForeachOr(pCtx, mask, pScan, pWhere, p1, pTail);
   689    705     }
   690    706   
   691    707     /* If no range/ORDER BY passed by the caller, create a version of the
   692    708     ** index for each range constraint that matches the mask. */
   693    709     if( pTail==0 ){
   694    710       for(pCon=pWhere->pRange; rc==SQLITE_OK && pCon; pCon=pCon->pNext){
   695    711         assert( pCon->pLink==0 );
   696    712         if( (mask & pCon->depmask)==pCon->depmask ){
   697         -        rc = idxCreateFromCons(dbm, pScan, p1, pCon);
          713  +        rc = idxCreateFromCons(pCtx, pScan, p1, pCon);
   698    714           if( rc==SQLITE_OK ){
   699         -          rc = idxCreateForeachOr(dbm, mask, pScan, pWhere, p1, pCon);
          715  +          rc = idxCreateForeachOr(pCtx, mask, pScan, pWhere, p1, pCon);
   700    716           }
   701    717         }
   702    718       }
   703    719     }
   704    720   
   705    721     return rc;
   706    722   }
   707    723   
   708    724   /*
   709    725   ** Create candidate indexes in database [dbm] based on the data in 
   710    726   ** linked-list pScan.
   711    727   */
   712         -static int idxCreateCandidates(
   713         -  sqlite3 *dbm,
   714         -  IdxScan *pScan,
   715         -  char **pzErrmsg
   716         -){
          728  +static int idxCreateCandidates(IdxContext *pCtx){
          729  +  sqlite3 *dbm = pCtx->dbm;
   717    730     int rc2;
   718    731     int rc = SQLITE_OK;
   719    732     sqlite3_stmt *pDepmask;         /* Foreach depmask */
   720    733     IdxScan *pIter;
   721    734   
   722         -  rc = idxPrepareStmt(dbm, &pDepmask, pzErrmsg, "SELECT mask FROM depmask");
          735  +  rc = idxPrepareStmt(pCtx->dbm, &pDepmask, pCtx->pzErrmsg, 
          736  +      "SELECT mask FROM depmask"
          737  +  );
   723    738   
   724         -  for(pIter=pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
          739  +  for(pIter=pCtx->pScan; pIter && rc==SQLITE_OK; pIter=pIter->pNextScan){
   725    740       IdxWhere *pWhere = &pIter->where;
   726    741       while( SQLITE_ROW==sqlite3_step(pDepmask) && rc==SQLITE_OK ){
   727    742         i64 mask = sqlite3_column_int64(pDepmask, 0);
   728         -      rc = idxCreateFromWhere(dbm, mask, pIter, pWhere, 0, 0);
          743  +      rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, 0);
   729    744         if( rc==SQLITE_OK && pIter->pOrder ){
   730         -        rc = idxCreateFromWhere(dbm, mask, pIter, pWhere, 0, pIter->pOrder);
          745  +        rc = idxCreateFromWhere(pCtx, mask, pIter, pWhere, 0, pIter->pOrder);
   731    746         }
   732    747       }
   733    748     }
   734    749   
   735    750     rc2 = sqlite3_finalize(pDepmask);
   736    751     if( rc==SQLITE_OK ) rc = rc2;
   737    752     return rc;
................................................................................
   743    758     for(pIter=pScan; pIter; pIter=pNext){
   744    759       pNext = pIter->pNextScan;
   745    760   
   746    761     }
   747    762   }
   748    763   
   749    764   int idxFindIndexes(
   750         -  sqlite3 *dbm,                        /* Database handle */
          765  +  IdxContext *pCtx,
   751    766     const char *zSql,                    /* SQL to find indexes for */
   752    767     void (*xOut)(void*, const char*),    /* Output callback */
   753    768     void *pOutCtx,                       /* Context for xOut() */
   754    769     char **pzErr                         /* OUT: Error message (sqlite3_malloc) */
   755    770   ){
          771  +  sqlite3 *dbm = pCtx->dbm;
   756    772     sqlite3_stmt *pExplain = 0;
   757    773     sqlite3_stmt *pSelect = 0;
   758    774     int rc, rc2;
          775  +  int bFound = 0;
   759    776   
   760    777     rc = idxPrintfPrepareStmt(dbm, &pExplain, pzErr,"EXPLAIN QUERY PLAN %s",zSql);
   761    778     if( rc==SQLITE_OK ){
   762    779       rc = idxPrepareStmt(dbm, &pSelect, pzErr, 
   763         -        "SELECT sql FROM sqlite_master WHERE name = ?"
          780  +        "SELECT rowid, sql FROM sqlite_master WHERE name = ?"
   764    781       );
   765    782     }
   766    783   
   767    784     while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
   768    785       int i;
   769    786       const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
   770    787       int nDetail = strlen(zDetail);
................................................................................
   772    789       for(i=0; i<nDetail; i++){
   773    790         if( memcmp(&zDetail[i], " USING INDEX ", 13)==0 ){
   774    791           int nIdx = 0;
   775    792           const char *zIdx = &zDetail[i+13];
   776    793           while( zIdx[nIdx]!='\0' && zIdx[nIdx]!=' ' ) nIdx++;
   777    794           sqlite3_bind_text(pSelect, 1, zIdx, nIdx, SQLITE_STATIC);
   778    795           if( SQLITE_ROW==sqlite3_step(pSelect) ){
   779         -          xOut(pOutCtx, sqlite3_column_text(pSelect, 0));
          796  +          i64 iRowid = sqlite3_column_int64(pSelect, 0);
          797  +          const char *zSql = (const char*)sqlite3_column_text(pSelect, 1);
          798  +          if( iRowid>=pCtx->iIdxRowid ){
          799  +            xOut(pOutCtx, zSql);
          800  +            bFound = 1;
          801  +          }
   780    802           }
   781    803           rc = sqlite3_reset(pSelect);
   782    804           break;
   783    805         }
   784    806       }
   785    807     }
   786    808     rc2 = sqlite3_reset(pExplain);
   787    809     if( rc==SQLITE_OK ) rc = rc2;
   788         -  if( rc==SQLITE_OK ) xOut(pOutCtx, "");
          810  +  if( rc==SQLITE_OK ){
          811  +    if( bFound==0 ) xOut(pOutCtx, "(no new indexes)");
          812  +    xOut(pOutCtx, "");
          813  +  }
   789    814   
   790    815     while( rc==SQLITE_OK && sqlite3_step(pExplain)==SQLITE_ROW ){
   791    816       int iSelectid = sqlite3_column_int(pExplain, 0);
   792    817       int iOrder = sqlite3_column_int(pExplain, 1);
   793    818       int iFrom = sqlite3_column_int(pExplain, 2);
   794    819       const char *zDetail = (const char*)sqlite3_column_text(pExplain, 3);
   795    820       char *zOut;
................................................................................
   837    862     ** database (handle db). The attached in-memory db (aux) contains
   838    863     ** application tables used by the code in this file.  */
   839    864     rc = sqlite3_open(":memory:", &dbm);
   840    865     if( rc==SQLITE_OK ){
   841    866       rc = sqlite3_exec(dbm, 
   842    867           "ATTACH ':memory:' AS aux;"
   843    868           "CREATE TABLE aux.depmask(mask PRIMARY KEY) WITHOUT ROWID;"
          869  +        "CREATE TABLE aux.indexes(name PRIMARY KEY) WITHOUT ROWID;"
   844    870           "INSERT INTO aux.depmask VALUES(0);"
   845    871           , 0, 0, pzErrmsg
   846    872       );
   847    873     }
   848    874   
   849    875     /* Prepare an INSERT statement for writing to aux.depmask */
   850    876     if( rc==SQLITE_OK ){
................................................................................
   868    894     ** removed. */
   869    895     if( rc==SQLITE_OK ){
   870    896       rc = idxCreateTables(db, dbm, ctx.pScan, pzErrmsg);
   871    897     }
   872    898   
   873    899     /* Create candidate indexes within the in-memory database file */
   874    900     if( rc==SQLITE_OK ){
   875         -    rc = idxCreateCandidates(dbm, ctx.pScan, pzErrmsg);
          901  +    rc = idxCreateCandidates(&ctx);
   876    902     }
   877    903   
   878    904     /* Figure out which of the candidate indexes are preferred by the query
   879    905     ** planner and report the results to the user.  */
   880    906     if( rc==SQLITE_OK ){
   881         -    rc = idxFindIndexes(dbm, zSql, xOut, pOutCtx, pzErrmsg);
          907  +    rc = idxFindIndexes(&ctx, zSql, xOut, pOutCtx, pzErrmsg);
   882    908     }
   883    909   
   884    910     idxScanFree(ctx.pScan);
   885    911     sqlite3_finalize(ctx.pInsertMask);
   886    912     sqlite3_close(dbm);
   887    913     return rc;
   888    914   }
   889    915   
   890    916