1 /*
2 ** 2018-12-04
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
10 **
11 *************************************************************************
12 **
13 ** This file implements a utility program used to help determine which
14 ** indexes in a database schema are used and unused, and how often specific
15 ** indexes are used.
16 */
17 #include "sqlite3.h"
18 #include <stdio.h>
19 #include <stdlib.h>
20 #include <assert.h>
21 #include <string.h>
22
usage(const char * argv0)23 static void usage(const char *argv0){
24 printf("Usage: %s [OPTIONS] DATABASE LOG\n\n", argv0);
25 printf(
26 "DATABASE is an SQLite database against which various statements\n"
27 "have been run. The SQL text is stored in LOG. LOG is an SQLite\n"
28 "database with this schema:\n"
29 "\n"
30 " CREATE TABLE sqllog(sql TEXT);\n"
31 "\n"
32 "This utility program analyzes statements contained in LOG and prints\n"
33 "a report showing how many times each index in DATABASE is used by the\n"
34 "statements in LOG.\n"
35 "\n"
36 "DATABASE only needs to contain the schema used by the statements in\n"
37 "LOG. The content can be removed from DATABASE.\n"
38 );
39 printf(
40 "\nOPTIONS:\n\n"
41 " --progress N Show a progress message after every N input rows\n"
42 " -q Omit error message when parsing log entries\n"
43 " --using NAME Print SQL statements that use index NAME\n"
44 );
45 printf("\nAnalysis will be done by SQLite version %s dated %.20s\n"
46 "checkin number %.40s. Different versions\n"
47 "of SQLite might use different indexes.\n",
48 sqlite3_libversion(), sqlite3_sourceid(), sqlite3_sourceid()+21);
49 exit(1);
50 }
51
main(int argc,char ** argv)52 int main(int argc, char **argv){
53 sqlite3 *db = 0; /* The main database */
54 sqlite3_stmt *pStmt = 0; /* a query */
55 char *zSql;
56 int nErr = 0;
57 int rc;
58 int bQuiet = 0;
59 int i, j;
60 const char *zUsing = 0;
61 sqlite3_stmt *pIncrCnt = 0;
62 int nRow = 0;
63 int iProgress = 0;
64
65 for(i=j=1; i<argc; i++){
66 const char *z = argv[i];
67 if( z[0]=='-' ){
68 z++;
69 if( z[0]=='-' ) z++;
70 if( strcmp(z,"progress")==0 ){
71 if( i+1<argc ){
72 iProgress = strtol(argv[++i],0,0);
73 continue;
74 }
75 printf("The --progress option requires an argument\n");
76 exit(0);
77 }
78 if( strcmp(z,"q")==0 ){
79 bQuiet = 1;
80 continue;
81 }
82 if( strcmp(z,"using")==0 ){
83 if( i+1<argc ){
84 zUsing = argv[++i];
85 continue;
86 }
87 printf("The --using option requires an argument\n");
88 exit(0);
89 }
90 if( strcmp(z, "help")==0 || strcmp(z, "?")==0 ){
91 usage(argv[0]);
92 }
93 printf("Unknown command-line option: \"%s\"\n", argv[i]);
94 exit(0);
95 }else{
96 if( j<i ) argv[j++] = argv[i];
97 }
98 }
99 argc = j;
100
101 if( argc!=3 ) usage(argv[0]);
102 rc = sqlite3_open_v2(argv[1], &db, SQLITE_OPEN_READONLY, 0);
103 if( rc ){
104 printf("Cannot open \"%s\" for reading: %s\n", argv[1], sqlite3_errmsg(db));
105 goto errorOut;
106 }
107 rc = sqlite3_prepare_v2(db, "SELECT * FROM sqlite_schema", -1, &pStmt, 0);
108 if( rc ){
109 printf("Cannot read the schema from \"%s\" - %s\n", argv[1],
110 sqlite3_errmsg(db));
111 goto errorOut;
112 }
113 sqlite3_finalize(pStmt);
114 pStmt = 0;
115 rc = sqlite3_exec(db,
116 "CREATE TABLE temp.idxu(\n"
117 " tbl TEXT COLLATE nocase,\n"
118 " idx TEXT COLLATE nocase,\n"
119 " cnt INT,\n"
120 " PRIMARY KEY(idx)\n"
121 ") WITHOUT ROWID;", 0, 0, 0);
122 if( rc ){
123 printf("Cannot create the result table - %s\n",
124 sqlite3_errmsg(db));
125 goto errorOut;
126 }
127 rc = sqlite3_exec(db,
128 "INSERT INTO temp.idxu(tbl,idx,cnt)"
129 " SELECT tbl_name, name, 0 FROM sqlite_schema"
130 " WHERE type='index' AND sql IS NOT NULL", 0, 0, 0);
131
132 /* Open the LOG database */
133 zSql = sqlite3_mprintf("ATTACH %Q AS log", argv[2]);
134 rc = sqlite3_exec(db, zSql, 0, 0, 0);
135 sqlite3_free(zSql);
136 if( rc ){
137 printf("Cannot open the LOG database \"%s\" - %s\n",
138 argv[2], sqlite3_errmsg(db));
139 goto errorOut;
140 }
141 rc = sqlite3_prepare_v2(db,
142 "SELECT sql, rowid FROM log.sqllog"
143 " WHERE upper(substr(sql,1,5)) NOT IN ('BEGIN','COMMI','ROLLB','PRAGM')",
144 -1, &pStmt, 0);
145 if( rc ){
146 printf("Cannot read the SQLLOG table in the LOG database \"%s\" - %s\n",
147 argv[2], sqlite3_errmsg(db));
148 goto errorOut;
149 }
150
151 rc = sqlite3_prepare_v2(db,
152 "UPDATE temp.idxu SET cnt=cnt+1 WHERE idx=?1",
153 -1, &pIncrCnt, 0);
154 if( rc ){
155 printf("Cannot prepare a statement to increment a counter for "
156 "indexes used\n");
157 goto errorOut;
158 }
159
160 /* Update the counts based on LOG */
161 while( sqlite3_step(pStmt)==SQLITE_ROW ){
162 const char *zLog = (const char*)sqlite3_column_text(pStmt, 0);
163 sqlite3_stmt *pS2;
164 if( zLog==0 ) continue;
165 zSql = sqlite3_mprintf("EXPLAIN QUERY PLAN %s", zLog);
166 rc = sqlite3_prepare_v2(db, zSql, -1, &pS2, 0);
167 sqlite3_free(zSql);
168 if( rc ){
169 if( !bQuiet ){
170 printf("Cannot compile LOG entry %d (%s): %s\n",
171 sqlite3_column_int(pStmt, 1), zLog, sqlite3_errmsg(db));
172 fflush(stdout);
173 }
174 nErr++;
175 }else{
176 nRow++;
177 if( iProgress>0 && (nRow%iProgress)==0 ){
178 printf("%d...\n", nRow);
179 fflush(stdout);
180 }
181 while( sqlite3_step(pS2)==SQLITE_ROW ){
182 const char *zExplain = (const char*)sqlite3_column_text(pS2,3);
183 const char *z1, *z2;
184 int n;
185 /* printf("EXPLAIN: %s\n", zExplain); */
186 z1 = strstr(zExplain, " USING INDEX ");
187 if( z1==0 ) continue;
188 z1 += 13;
189 for(z2=z1+1; z2[0] && z2[1]!='('; z2++){}
190 n = z2 - z1;
191 if( zUsing && sqlite3_strnicmp(zUsing, z1, n)==0 ){
192 printf("Using %s:\n%s\n", zUsing, zLog);
193 fflush(stdout);
194 }
195 sqlite3_bind_text(pIncrCnt,1,z1,n,SQLITE_STATIC);
196 sqlite3_step(pIncrCnt);
197 sqlite3_reset(pIncrCnt);
198 }
199 }
200 sqlite3_finalize(pS2);
201 }
202 sqlite3_finalize(pStmt);
203
204 /* Generate the report */
205 rc = sqlite3_prepare_v2(db,
206 "SELECT tbl, idx, cnt, "
207 " (SELECT group_concat(name,',') FROM pragma_index_info(idx))"
208 " FROM temp.idxu, main.sqlite_schema"
209 " WHERE temp.idxu.tbl=main.sqlite_schema.tbl_name"
210 " AND temp.idxu.idx=main.sqlite_schema.name"
211 " ORDER BY cnt DESC, tbl, idx",
212 -1, &pStmt, 0);
213 if( rc ){
214 printf("Cannot query the result table - %s\n",
215 sqlite3_errmsg(db));
216 goto errorOut;
217 }
218 while( sqlite3_step(pStmt)==SQLITE_ROW ){
219 printf("%10d %s on %s(%s)\n",
220 sqlite3_column_int(pStmt, 2),
221 sqlite3_column_text(pStmt, 1),
222 sqlite3_column_text(pStmt, 0),
223 sqlite3_column_text(pStmt, 3));
224 }
225 sqlite3_finalize(pStmt);
226 pStmt = 0;
227
228 errorOut:
229 sqlite3_finalize(pIncrCnt);
230 sqlite3_finalize(pStmt);
231 sqlite3_close(db);
232 return nErr;
233 }
234