/ Check-in [c6fa01c2]
Login

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

Overview
Comment:Add a hack to debug out a description of the WHERE clause of a SELECT (or other) statement. Use this in script tool/schemalint.tcl to automatically recommend indexes that might speed up specific queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | schemalint
Files: files | file ages | folders
SHA1: c6fa01c28ef7ceea2963a92dfffe62eed451b05c
User & Date: dan 2015-11-11 18:08:58
Context
2015-11-20
20:55
Add support for ORDER BY clauses to schemalint.tcl. check-in: 93bdf70e user: dan tags: schemalint
2015-11-11
18:08
Add a hack to debug out a description of the WHERE clause of a SELECT (or other) statement. Use this in script tool/schemalint.tcl to automatically recommend indexes that might speed up specific queries. check-in: c6fa01c2 user: dan tags: schemalint
15:28
Improvements to the SQLITE_CONFIG_PAGECACHE documentation. Enhance the command-line shell to be able to take advantage of the full range of SQLITE_CONFIG_PAGECACHE capabilities, such as setting pMem==NULL and N<0. check-in: 2518d5c9 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/where.c.

  3897   3897       pLoop->cId = '0';
  3898   3898   #endif
  3899   3899       return 1;
  3900   3900     }
  3901   3901     return 0;
  3902   3902   }
  3903   3903   
         3904  +#ifdef SQLITE_SCHEMA_LINT
         3905  +static char *whereAppendPrintf(sqlite3 *db, const char *zFmt, ...){
         3906  +  va_list ap;
         3907  +  char *zRes = 0;
         3908  +  va_start(ap, zFmt);
         3909  +  zRes = sqlite3_vmprintf(zFmt, ap);
         3910  +  if( zRes==0 ){
         3911  +    db->mallocFailed = 1;
         3912  +  }else if( db->mallocFailed ){
         3913  +    sqlite3_free(zRes);
         3914  +    zRes = 0;
         3915  +  }
         3916  +  va_end(ap);
         3917  +  return zRes;
         3918  +}
         3919  +
         3920  +/*
         3921  +** Append a representation of term pTerm to the string in zIn and return
         3922  +** the result. Or, if an OOM occurs, free zIn and return a NULL pointer.
         3923  +*/
         3924  +static char *whereAppendSingleTerm(
         3925  +  Parse *pParse,
         3926  +  Table *pTab,
         3927  +  int bOr,
         3928  +  char *zIn,
         3929  +  WhereTerm *pTerm
         3930  +){
         3931  +  char *zBuf;
         3932  +  sqlite3 *db = pParse->db;
         3933  +  Expr *pX = pTerm->pExpr;
         3934  +  CollSeq *pColl;
         3935  +  const char *zOp = 0;
         3936  +
         3937  +  if( pTerm->eOperator & (WO_IS|WO_EQ|WO_IN) ){
         3938  +    zOp = "eq";
         3939  +  }else if( pTerm->eOperator & (WO_LT|WO_LE|WO_GE|WO_GT) ){
         3940  +    zOp = "range";
         3941  +  }
         3942  +  pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
         3943  +
         3944  +  if( zOp ){
         3945  +    const char *zFmt = bOr ? "%z{{%s %s %s %lld}}" : "%z{%s %s %s %lld}";
         3946  +    zBuf = whereAppendPrintf(db, zFmt, zIn, 
         3947  +        zOp, pTab->aCol[pTerm->u.leftColumn].zName, 
         3948  +        (pColl ? pColl->zName : "BINARY"),
         3949  +        pTerm->prereqRight
         3950  +    );
         3951  +  }else{
         3952  +    zBuf = zIn;
         3953  +  }
         3954  +
         3955  +  return zBuf;
         3956  +}
         3957  +
         3958  +static char *whereTraceWC(
         3959  +  Parse *pParse, 
         3960  +  struct SrcList_item *pItem,
         3961  +  char *zIn,
         3962  +  WhereClause *pWC
         3963  +){
         3964  +  sqlite3 *db = pParse->db;
         3965  +  Table *pTab = pItem->pTab;
         3966  +  char *zBuf = zIn;
         3967  +  int iCol;
         3968  +  int ii;
         3969  +  int bFirst = 1;
         3970  +
         3971  +  /* List of WO_SINGLE constraints */
         3972  +  for(iCol=0; iCol<pTab->nCol; iCol++){
         3973  +    int opMask = WO_SINGLE; 
         3974  +    WhereScan scan;
         3975  +    WhereTerm *pTerm;
         3976  +    for(pTerm=whereScanInit(&scan, pWC, pItem->iCursor, iCol, opMask, 0);
         3977  +        pTerm;
         3978  +        pTerm=whereScanNext(&scan)
         3979  +    ){
         3980  +      assert( iCol==pTerm->u.leftColumn );
         3981  +      if( bFirst==0 ) zBuf = whereAppendPrintf(db, "%z ", zBuf);
         3982  +      zBuf = whereAppendSingleTerm(pParse, pTab, pWC->op==TK_OR, zBuf, pTerm);
         3983  +      bFirst = 0;
         3984  +    }
         3985  +  }
         3986  +
         3987  +  /* Add composite - (WO_OR|WO_AND) - constraints */
         3988  +  for(ii=0; ii<pWC->nTerm; ii++){
         3989  +    WhereTerm *pTerm = &pWC->a[ii];
         3990  +    if( pTerm->eOperator & (WO_OR|WO_AND) ){
         3991  +      const char *zFmt = ((pTerm->eOperator&WO_OR) ? "%z%s{or " : "%z%s{");
         3992  +      zBuf = whereAppendPrintf(db, zFmt, zBuf, bFirst ? "" : " ");
         3993  +      zBuf = whereTraceWC(pParse, pItem, zBuf, &pTerm->u.pOrInfo->wc);
         3994  +      zBuf = whereAppendPrintf(db, "%z}", zBuf);
         3995  +      bFirst = 0;
         3996  +    }
         3997  +  }
         3998  +
         3999  +  return zBuf;
         4000  +}
         4001  +
         4002  +static void whereTraceBuilder(
         4003  +  Parse *pParse,
         4004  +  WhereLoopBuilder *p
         4005  +){
         4006  +  sqlite3 *db = pParse->db;
         4007  +  if( db->xTrace ){
         4008  +    WhereInfo *pWInfo = p->pWInfo;
         4009  +    int nTablist = pWInfo->pTabList->nSrc;
         4010  +    int ii;
         4011  +
         4012  +    /* Loop through each element of the FROM clause. Ignore any sub-selects
         4013  +    ** or views. Invoke the xTrace() callback once for each real table. */
         4014  +    for(ii=0; ii<nTablist; ii++){
         4015  +      char *zBuf = 0;
         4016  +      int iCol;
         4017  +      int nCol;
         4018  +      Table *pTab;
         4019  +
         4020  +      struct SrcList_item *pItem = &pWInfo->pTabList->a[ii];
         4021  +      if( pItem->pSelect ) continue;
         4022  +      pTab = pItem->pTab;
         4023  +      nCol = pTab->nCol;
         4024  +
         4025  +      /* Append the table name to the buffer. */
         4026  +      zBuf = whereAppendPrintf(db, "%s", pTab->zName);
         4027  +
         4028  +      /* Append the list of columns required to create a covering index */
         4029  +      zBuf = whereAppendPrintf(db, "%z {cols", zBuf);
         4030  +      if( 0==(pItem->colUsed & ((u64)1 << (sizeof(Bitmask)*8-1))) ){
         4031  +        for(iCol=0; iCol<nCol; iCol++){
         4032  +          if( iCol==(sizeof(Bitmask)*8-1) ) break;
         4033  +          if( pItem->colUsed & ((u64)1 << iCol) ){
         4034  +            zBuf = whereAppendPrintf(db, "%z %s", zBuf, pTab->aCol[iCol].zName);
         4035  +          }
         4036  +        }
         4037  +      }
         4038  +      zBuf = whereAppendPrintf(db, "%z} ", zBuf);
         4039  +
         4040  +      /* Append the contents of WHERE clause */
         4041  +      zBuf = whereTraceWC(pParse, pItem, zBuf, p->pWC);
         4042  +
         4043  +      /* Pass the buffer to the xTrace() callback, then free it */
         4044  +      db->xTrace(db->pTraceArg, zBuf);
         4045  +      sqlite3DbFree(db, zBuf);
         4046  +    }
         4047  +  }
         4048  +}
         4049  +#else
         4050  +# define whereTraceBuilder(x,y)
         4051  +#endif
         4052  +
  3904   4053   /*
  3905   4054   ** Generate the beginning of the loop used for WHERE clause processing.
  3906   4055   ** The return value is a pointer to an opaque structure that contains
  3907   4056   ** information needed to terminate the loop.  Later, the calling routine
  3908   4057   ** should invoke sqlite3WhereEnd() with the return value of this function
  3909   4058   ** in order to complete the WHERE clause processing.
  3910   4059   **
................................................................................
  4156   4305     if( sqlite3WhereTrace & 0x100 ){ /* Display all terms of the WHERE clause */
  4157   4306       int i;
  4158   4307       for(i=0; i<sWLB.pWC->nTerm; i++){
  4159   4308         whereTermPrint(&sWLB.pWC->a[i], i);
  4160   4309       }
  4161   4310     }
  4162   4311   #endif
         4312  +
         4313  +  /* Schema-lint xTrace callback */
         4314  +  whereTraceBuilder(pParse, &sWLB);
  4163   4315   
  4164   4316     if( nTabList!=1 || whereShortCut(&sWLB)==0 ){
  4165   4317       rc = whereLoopAddAll(&sWLB);
  4166   4318       if( rc ) goto whereBeginError;
  4167   4319     
  4168   4320   #ifdef WHERETRACE_ENABLED
  4169   4321       if( sqlite3WhereTrace ){    /* Display all of the WhereLoop objects */

Added test/schemalint.test.

            1  +
            2  +
            3  +set testdir [file dirname $argv0]
            4  +source $testdir/tester.tcl
            5  +set testprefix schemalint
            6  +
            7  +proc xTrace {zMsg} {
            8  +  lappend ::trace_out $zMsg
            9  +}
           10  +db trace xTrace
           11  +
           12  +proc do_trace_test {tn sql res} {
           13  +  uplevel [list do_test $tn [subst -nocommands {
           14  +    set ::trace_out [list]
           15  +    set stmt [sqlite3_prepare db "$sql" -1 x]
           16  +    sqlite3_finalize [set stmt]
           17  +    set ::trace_out
           18  +  }] [list {*}$res]]
           19  +}
           20  +
           21  +
           22  +do_execsql_test 1.0 {
           23  +  CREATE TABLE t1(a, b, c);
           24  +  CREATE TABLE t2(x, y, z);
           25  +}
           26  +
           27  +do_trace_test 1.1 {
           28  +  SELECT b, c, y, z FROM t1, t2 WHERE c=? AND z=?
           29  +} {
           30  +  {t1 {cols b c} {eq c BINARY 0}}
           31  +  {t2 {cols y z} {eq z BINARY 0}}
           32  +}
           33  +
           34  +do_trace_test 1.2 {
           35  +  SELECT a FROM t1 WHERE b>10
           36  +} {
           37  +  {t1 {cols a b} {range b BINARY 0}}
           38  +}
           39  +
           40  +do_trace_test 1.3 {
           41  +  SELECT b FROM t1 WHERE b IN (10, 20, 30)
           42  +} {
           43  +  {t1 {cols b} {eq b BINARY 0}}
           44  +}
           45  +
           46  +do_trace_test 1.4 {
           47  +  SELECT * FROM t1, t2 WHERE x=a
           48  +} {
           49  +  {t1 {cols a b c} {eq a BINARY 2}} 
           50  +  {t2 {cols x y z} {eq x BINARY 1}}
           51  +}
           52  +
           53  +do_trace_test 1.5 {
           54  +  SELECT * FROM t1 WHERE a IN (1, 2, 3)
           55  +} {
           56  +  {t1 {cols a b c} {eq a BINARY 0}}
           57  +}
           58  +
           59  +#-----------------------------------------------------------------------
           60  +# Cases involving OR clauses in the WHERE clause.
           61  +#
           62  +do_trace_test 2.1 {
           63  +  SELECT * FROM t1 WHERE a=? OR b=?
           64  +} {
           65  +  {t1 {cols a b c} {or {{eq a BINARY 0}} {{eq b BINARY 0}}}}
           66  +}
           67  +
           68  +do_trace_test 2.2 {
           69  +  SELECT * FROM t1 WHERE a=? OR (b=? AND c=?)
           70  +} {
           71  +  {t1 {cols a b c} {or {{eq a BINARY 0}} {{eq b BINARY 0} {eq c BINARY 0}}}}
           72  +}
           73  +
           74  +do_trace_test 2.3 {
           75  +  SELECT * FROM t1 WHERE (a=? AND b=?) OR c=?
           76  +} {
           77  +  {t1 {cols a b c} {or {{eq c BINARY 0}} {{eq a BINARY 0} {eq b BINARY 0}}}}
           78  +}
           79  +
           80  +finish_test
           81  +

Added tool/schemalint.tcl.

            1  +
            2  +
            3  +
            4  +set ::G(lSelect)  [list]           ;# List of SELECT statements to analyze
            5  +set ::G(database) ""               ;# Name of database or SQL schema file
            6  +set ::G(trace)    [list]           ;# List of data from xTrace()
            7  +set ::G(verbose)  0                ;# True if -verbose option was passed 
            8  +
            9  +proc usage {} {
           10  +  puts stderr "Usage: $::argv0 ?SWITCHES? DATABASE/SCHEMA"
           11  +  puts stderr "  Switches are:"
           12  +  puts stderr "  -select SQL     (recommend indexes for SQL statement)"
           13  +  puts stderr "  -verbose        (increase verbosity of output)"
           14  +  puts stderr ""
           15  +  exit
           16  +}
           17  +
           18  +proc process_cmdline_args {argv} {
           19  +  global G
           20  +  set nArg [llength $argv]
           21  +  set G(database) [lindex $argv end]
           22  +
           23  +  for {set i 0} {$i < [llength $argv]-1} {incr i} {
           24  +    set k [lindex $argv $i]
           25  +    switch -- $k {
           26  +      -select {
           27  +        incr i
           28  +        if {$i>=[llength $argv]-1} usage
           29  +        lappend G(lSelect) [lindex $argv $i]
           30  +      }
           31  +      -verbose {
           32  +        set G(verbose) 1
           33  +      }
           34  +      default {
           35  +        usage
           36  +      }
           37  +    }
           38  +  }
           39  +}
           40  +
           41  +proc open_database {} {
           42  +  global G
           43  +  sqlite3 db ""
           44  +
           45  +  # Check if the "database" file is really an SQLite database. If so, copy
           46  +  # it into the temp db just opened. Otherwise, assume that it is an SQL
           47  +  # schema and execute it directly.
           48  +  set fd [open $G(database)]
           49  +  set hdr [read $fd 16]
           50  +  if {$hdr == "SQLite format 3\000"} {
           51  +    close $fd
           52  +    sqlite3 db2 $G(database)
           53  +    sqlite3_backup B db main db2 main
           54  +    B step 2000000000
           55  +    set rc [B finish]
           56  +    db2 close
           57  +    if {$rc != "SQLITE_OK"} { error "Failed to load database $G(database)" }
           58  +  } else {
           59  +    append hdr [read $fd]
           60  +    db eval $hdr
           61  +    close $fd
           62  +  }
           63  +}
           64  +
           65  +proc analyze_selects {} {
           66  +  global G
           67  +  set G(trace) ""
           68  +
           69  +  # Collect a line of xTrace output for each loop in the set of SELECT
           70  +  # statements.
           71  +  proc xTrace {zMsg} { lappend ::G(trace) $zMsg }
           72  +  db trace "lappend ::G(trace)"
           73  +  foreach s $G(lSelect) {
           74  +    set stmt [sqlite3_prepare_v2 db $s -1 dummy]
           75  +    set rc [sqlite3_finalize $stmt]
           76  +    if {$rc!="SQLITE_OK"} {
           77  +      error "Failed to compile SQL: [sqlite3_errmsg db]"
           78  +    }
           79  +  }
           80  +
           81  +  db trace ""
           82  +  if {$G(verbose)} {
           83  +    foreach t $G(trace) { puts "trace: $t" }
           84  +  }
           85  +
           86  +  # puts $G(trace)
           87  +}
           88  +
           89  +# The argument is a list of the form:
           90  +#
           91  +#    key1 {value1.1 value1.2} key2 {value2.1 value 2.2...}
           92  +#
           93  +# Values lists may be of any length greater than zero. This function returns
           94  +# a list of lists created by pivoting on each values list. i.e. a list
           95  +# consisting of the elements:
           96  +#
           97  +#   {{key1 value1.1} {key2 value2.1}}
           98  +#   {{key1 value1.2} {key2 value2.1}}
           99  +#   {{key1 value1.1} {key2 value2.2}}
          100  +#   {{key1 value1.2} {key2 value2.2}}
          101  +#
          102  +proc expand_eq_list {L} {
          103  +  set ll [list {}]
          104  +  for {set i 0} {$i < [llength $L]} {incr i 2} {
          105  +    set key [lindex $L $i]
          106  +    set new [list]
          107  +    foreach piv [lindex $L $i+1] {
          108  +      foreach l $ll {
          109  +        lappend new [concat $l [list [list $key $piv]]]
          110  +      }
          111  +    }
          112  +    set ll $new
          113  +  }
          114  +
          115  +  return $ll
          116  +}
          117  +
          118  +proc eqset_to_index {tname eqset {range {}}} {
          119  +  global G
          120  +  set lCols [list]
          121  +  set idxname $tname
          122  +  foreach e [concat [lsort $eqset] [list $range]] {
          123  +    if {[llength $e]==0} continue
          124  +    foreach {c collate} $e {}
          125  +    lappend lCols "$c collate $collate"
          126  +    append idxname "_$c"
          127  +    if {[string compare -nocase binary $collate]!=0} {
          128  +      append idxname [string tolower $collate]
          129  +    }
          130  +  }
          131  +
          132  +  set create_index "CREATE INDEX $idxname ON ${tname}("
          133  +  append create_index [join $lCols ", "]
          134  +  append create_index ");"
          135  +
          136  +  set G(trial.$idxname) $create_index
          137  +}
          138  +
          139  +proc expand_or_cons {L} {
          140  +  set lRet [list [list]]
          141  +  foreach elem $L {
          142  +    set type [lindex $elem 0]
          143  +    if {$type=="eq" || $type=="range"} {
          144  +      set lNew [list]
          145  +      for {set i 0} {$i < [llength $lRet]} {incr i} {
          146  +        lappend lNew [concat [lindex $lRet $i] [list $elem]]
          147  +      }
          148  +      set lRet $lNew
          149  +    } elseif {$type=="or"} {
          150  +      set lNew [list]
          151  +      foreach branch [lrange $elem 1 end] {
          152  +        foreach b [expand_or_cons $branch] {
          153  +          for {set i 0} {$i < [llength $lRet]} {incr i} {
          154  +            lappend lNew [concat [lindex $lRet $i] $b]
          155  +          }
          156  +        }
          157  +      }
          158  +      set lRet $lNew
          159  +    } 
          160  +  }
          161  +  return $lRet
          162  +}
          163  +
          164  +proc find_trial_indexes {} {
          165  +  global G
          166  +  foreach t $G(trace) {
          167  +    set tname [lindex $t 0]
          168  +    catch { array unset mask }
          169  +
          170  +    foreach lCons [expand_or_cons [lrange $t 2 end]] {
          171  +      set constraints [list]
          172  +
          173  +      foreach a $lCons {
          174  +        set type [lindex $a 0]
          175  +        if {$type=="eq" || $type=="range"} {
          176  +          set m [lindex $a 3]
          177  +          foreach k [array names mask] { set mask([expr ($k & $m)]) 1 }
          178  +          set mask($m) 1
          179  +          lappend constraints $a
          180  +        }
          181  +      }
          182  +
          183  +      foreach k [array names mask] {
          184  +        catch {array unset eq}
          185  +        foreach a $constraints {
          186  +          foreach {type col collate m} $a {
          187  +            if {($m & $k)==$m} {
          188  +              if {$type=="eq"} {
          189  +                lappend eq($col) $collate
          190  +              } else {
          191  +                set range($col.$collate) 1
          192  +              }
          193  +            }
          194  +          }
          195  +        }
          196  +
          197  +        #puts "mask=$k eq=[array get eq] range=[array get range]"
          198  +        
          199  +        set ranges [array names range]
          200  +        foreach eqset [expand_eq_list [array get eq]] {
          201  +          if {[llength $ranges]==0} {
          202  +            eqset_to_index $tname $eqset
          203  +          } else {
          204  +            foreach r $ranges {
          205  +              set bSeen 0
          206  +              foreach {c collate} [split $r .] {}
          207  +              foreach e $eqset {
          208  +                if {[lindex $e 0] == $c} {
          209  +                  set bSeen 1
          210  +                  break
          211  +                }
          212  +              }
          213  +              if {$bSeen} {
          214  +                eqset_to_index $tname $eqset
          215  +              } else {
          216  +                eqset_to_index $tname $eqset [list $c $collate]
          217  +              }
          218  +            }
          219  +          }
          220  +        }
          221  +      }
          222  +    }
          223  +  }
          224  +
          225  +  if {$G(verbose)} {
          226  +    foreach k [array names G trial.*] { puts "index: $G($k)" }
          227  +  }
          228  +}
          229  +
          230  +proc run_trials {} {
          231  +  global G
          232  +
          233  +  foreach k [array names G trial.*] {
          234  +    set idxname [lindex [split $k .] 1]
          235  +    db eval $G($k)
          236  +    set pgno [db one {SELECT rootpage FROM sqlite_master WHERE name = $idxname}]
          237  +    set IDX($pgno) $idxname
          238  +  }
          239  +  db eval ANALYZE
          240  +
          241  +  catch { array unset used }
          242  +  foreach s $G(lSelect) {
          243  +    db eval "EXPLAIN $s" x {
          244  +      if {($x(opcode)=="OpenRead" || $x(opcode)=="ReopenIdx")} {
          245  +        if {[info exists IDX($x(p2))]} { set used($IDX($x(p2))) 1 }
          246  +      }
          247  +    }
          248  +    foreach idx [array names used] {
          249  +      puts $G(trial.$idx)
          250  +    }
          251  +  }
          252  +}
          253  +
          254  +process_cmdline_args $argv
          255  +open_database
          256  +analyze_selects
          257  +find_trial_indexes
          258  +run_trials
          259  +