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: |
c6fa01c28ef7ceea2963a92dfffe62ee |
| 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
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 +