61 </codeblock> <p>Is roughly equivalent to the following: </p> <codeblock id="GUID-DB3167E0-FA95-50CA-92C7-102B5C2C13E3" xml:space="preserve"> |
64 </codeblock> <p>Is roughly equivalent to the following: </p> <codeblock id="GUID-DB3167E0-FA95-50CA-92C7-102B5C2C13E3" xml:space="preserve"> |
62 CREATE TABLE demo39b( |
65 CREATE TABLE demo39b( |
63 id INTEGER UNIQUE, |
66 id INTEGER UNIQUE, |
64 content BLOB |
67 content BLOB |
65 ); |
68 ); |
66 </codeblock> <p>The two examples above are “roughly” equivalent, but not exactly |
69 </codeblock> <p>The two examples above are “roughly” equivalent, but |
67 equivalent. Both tables have an index on the ID column. In the first case, |
70 not exactly equivalent. Both tables have an index on the ID column. |
68 the index is created explicitly. In the second case, the index is implied |
71 In the first case, the index is created explicitly. In the second |
69 by the UNIQUE keyword in the type declaration of the ID column. Both table |
72 case, the index is implied by the UNIQUE keyword in the type declaration |
70 designs use exactly the same amount of disk space, and both will run queries |
73 of the ID column. Both table designs use exactly the same amount of |
71 such as </p> <codeblock id="GUID-7ACAE270-6D20-557B-B7D1-C90EDD757E43" xml:space="preserve"> |
74 disk space, and both will run queries such as </p> <codeblock id="GUID-7ACAE270-6D20-557B-B7D1-C90EDD757E43" xml:space="preserve"> |
72 SELECT content FROM demo39 WHERE id=? |
75 SELECT content FROM demo39 WHERE id=? |
73 </codeblock> <p>using exactly the same bytecode. The only difference is that |
76 </codeblock> <p>using exactly the same bytecode. The only difference |
74 table demo39a lets you insert multiple rows with the same ID whereas table |
77 is that table demo39a lets you insert multiple rows with the same |
75 demo39b will raise an exception if you try to insert a new row with the same |
78 ID whereas table demo39b will raise an exception if you try to insert |
76 ID as an existing row. </p> <p>If you use the UNIQUE keyword in the CREATE |
79 a new row with the same ID as an existing row. </p> <p>If you use |
77 INDEX statement of demo39a, like this: </p> <codeblock id="GUID-0EE5E186-CC4A-5CC3-AEAE-F1482F1F8F9A" xml:space="preserve"> |
80 the UNIQUE keyword in the CREATE INDEX statement of demo39a, like |
|
81 this: </p> <codeblock id="GUID-0EE5E186-CC4A-5CC3-AEAE-F1482F1F8F9A" xml:space="preserve"> |
78 CREATE UNIQUE INDEX demo39_idx1 ON demo39a(id); |
82 CREATE UNIQUE INDEX demo39_idx1 ON demo39a(id); |
79 </codeblock> <p>Then both table designs really would be exactly the same in |
83 </codeblock> <p>Then both table designs really would be exactly the |
80 every way. In fact, whenever SQLite sees the UNIQUE keyword on a column type |
84 same in every way. In fact, whenever SQLite sees the UNIQUE keyword |
81 declaration, all it does is create an automatic unique index on that column. </p> <p>The |
85 on a column type declaration, all it does is create an automatic unique |
82 PRIMARY KEY modifier on a column type declaration works like UNIQUE; it causes |
86 index on that column. </p> <p>The PRIMARY KEY modifier on a column |
83 a unique index to be created automatically. The main difference is that you |
87 type declaration works like UNIQUE; it causes a unique index to be |
84 are only allowed to have a single PRIMARY KEY. This restriction of only allowing |
88 created automatically. The main difference is that you are only allowed |
85 a single PRIMARY KEY is part of the official SQL language definition. </p> <p>The |
89 to have a single PRIMARY KEY. This restriction of only allowing a |
86 idea is that a PRIMARY KEY is used to order the rows on disk. Some SQL database |
90 single PRIMARY KEY is part of the official SQL language definition. </p> <p>The idea is that a PRIMARY KEY is used to order the rows on disk. |
87 engines actually implement PRIMARY KEYs this way. But with SQLite, a PRIMARY |
91 Some SQL database engines actually implement PRIMARY KEYs this way. |
88 KEY is like any other UNIQUE column, with only one exception: INTEGER PRIMARY |
92 But with SQLite, a PRIMARY KEY is like any other UNIQUE column, with |
89 KEY is a special case which is handled differently, as described in the next |
93 only one exception: INTEGER PRIMARY KEY is a special case which is |
90 section. </p> </section> |
94 handled differently, as described in the next section. </p> </section> |
91 <section id="GUID-BF7A0301-8490-58ED-BB37-FAC403A84230"><title>Use Multi-Column |
95 <section id="GUID-BF7A0301-8490-58ED-BB37-FAC403A84230"><title>Use |
92 Indexes</title> <p>SQLite is able to make use of multi-column indexes. The |
96 Multi-Column Indexes</title> <p>SQLite is able to make use of multi-column |
93 rule is that if an index is over columns <i>X</i> <i> 0 </i>, <i>X</i> <i> 1 </i>, <i>X</i> <i> 2 </i>, |
97 indexes. The rule is that if an index is over columns <i>X</i> <i> 0 </i>, <i>X</i> <i> 1 </i>, <i>X</i> <i> 2 </i>, ..., <i>X</i> <i> n </i> of some table, then the index can be used if the WHERE |
94 ..., <i>X</i> <i> n </i> of some table, then the index can be used if the |
98 clause contains equality constraints for some prefix of those columns <i>X</i> <i>0 </i>, <i>X</i> <i>1 </i>, <i>X</i> <i>2 </i>, ..., <i>X</i> <i>i </i> where <i>i</i> is less than <i>n</i>. </p> <p>As |
95 WHERE clause contains equality constraints for some prefix of those columns <i>X</i> <i>0 </i>, <i>X</i> <i>1 </i>, <i>X</i> <i>2 </i>, |
|
96 ..., <i>X</i> <i>i </i> where <i>i</i> is less than <i>n</i>. </p> <p>As |
|
97 an example, suppose you have a table and index declared as follows: </p> <codeblock id="GUID-C18C97F7-23CA-5636-9F00-130A8FB3DEF5" xml:space="preserve"> |
99 an example, suppose you have a table and index declared as follows: </p> <codeblock id="GUID-C18C97F7-23CA-5636-9F00-130A8FB3DEF5" xml:space="preserve"> |
98 CREATE TABLE demo314(a,b,c,d,e,f,g); |
100 CREATE TABLE demo314(a,b,c,d,e,f,g); |
99 CREATE INDEX demo314_idx ON demo314(a,b,c,d,e,f); |
101 CREATE INDEX demo314_idx ON demo314(a,b,c,d,e,f); |
100 </codeblock> <p>Then the index might be used to help with a query that contained |
102 </codeblock> <p>Then the index might be used to help with a query |
101 a WHERE clause like this: </p> <codeblock id="GUID-8A0944F4-1ACF-5267-B49F-EB83EFBB5670" xml:space="preserve"> |
103 that contained a WHERE clause like this: </p> <codeblock id="GUID-8A0944F4-1ACF-5267-B49F-EB83EFBB5670" xml:space="preserve"> |
102 ... WHERE a=1 AND b='Smith' AND c=1 |
104 ... WHERE a=1 AND b='Smith' AND c=1 |
103 </codeblock> <p>All three terms of the WHERE clause would be used together |
105 </codeblock> <p>All three terms of the WHERE clause would be used |
104 with the index in order to narrow the search. But the index could not be used |
106 together with the index in order to narrow the search. But the index |
105 if there WHERE clause said: </p> <codeblock id="GUID-B5F1C17F-0F5E-5FC2-A9A4-DF19D699A076" xml:space="preserve"> |
107 could not be used if there WHERE clause said: </p> <codeblock id="GUID-B5F1C17F-0F5E-5FC2-A9A4-DF19D699A076" xml:space="preserve"> |
106 ... WHERE b='Smith' AND c=1 |
108 ... WHERE b='Smith' AND c=1 |
107 </codeblock> <p>The second WHERE clause does not contain equality terms for |
109 </codeblock> <p>The second WHERE clause does not contain equality |
108 a prefix of the columns in the index because it omits a term for the “a” column. </p> <p>In |
110 terms for a prefix of the columns in the index because it omits a |
109 a case like this: </p> <codeblock id="GUID-EF2CFE7D-0456-5414-847D-BADCC057CFD8" xml:space="preserve"> |
111 term for the “a” column. </p> <p>In a case like this: </p> <codeblock id="GUID-EF2CFE7D-0456-5414-847D-BADCC057CFD8" xml:space="preserve"> |
110 ... WHERE a=1 AND c=1 |
112 ... WHERE a=1 AND c=1 |
111 </codeblock> <p>Only the “a=1” term in the WHERE clause could be used to help |
113 </codeblock> <p>Only the “a=1” term in the WHERE clause could be used |
112 narrow the search. The “c=1” term is not part of the prefix of terms in the |
114 to help narrow the search. The “c=1” term is not part of the prefix |
113 index which have equality constraints because there is no equality constraint |
115 of terms in the index which have equality constraints because there |
114 on the “b” column. </p> <p>SQLite only allows a single index to be used per |
116 is no equality constraint on the “b” column. </p> <p>SQLite only allows |
115 table within a simple SQL statement. For UPDATE and DELETE statements, this |
117 a single index to be used per table within a simple SQL statement. |
116 means that only a single index can ever be used, since those statements can |
118 For UPDATE and DELETE statements, this means that only a single index |
117 only operate on a single table at a time. </p> <p>In a simple SELECT statement |
119 can ever be used, since those statements can only operate on a single |
118 multiple indexes can be used if the SELECT statement is a join – one index |
120 table at a time. </p> <p>In a simple SELECT statement multiple indexes |
119 per table in the join. In a compound SELECT statement (two or more SELECT |
121 can be used if the SELECT statement is a join – one index per table |
120 statements connected by UNION or INTERSECT or EXCEPT) each SELECT statement |
122 in the join. In a compound SELECT statement (two or more SELECT statements |
121 is treated separately and can have its own indexes. Likewise, SELECT statements |
123 connected by UNION or INTERSECT or EXCEPT) each SELECT statement is |
122 that appear in subexpressions are treated separately. </p> <p>Some other SQL |
124 treated separately and can have its own indexes. Likewise, SELECT |
123 database engines (for example PostgreSQL) allow multiple indexes to be used |
125 statements that appear in subexpressions are treated separately. </p> <p>Some other SQL database engines (for example PostgreSQL) allow |
124 for each table in a SELECT. For example, if you had a table and index in PostgreSQL |
126 multiple indexes to be used for each table in a SELECT. For example, |
125 like this: </p> <codeblock id="GUID-F5DE8F24-7471-5992-9896-295CE173D855" xml:space="preserve"> |
127 if you had a table and index in PostgreSQL like this: </p> <codeblock id="GUID-F5DE8F24-7471-5992-9896-295CE173D855" xml:space="preserve"> |
126 CREATE TABLE pg1(a INT, b INT, c INT, d INT); |
128 CREATE TABLE pg1(a INT, b INT, c INT, d INT); |
127 CREATE INDEX pg1_ix1 ON pg1(a); |
129 CREATE INDEX pg1_ix1 ON pg1(a); |
128 CREATE INDEX pg1_ix2 ON pg1(b); |
130 CREATE INDEX pg1_ix2 ON pg1(b); |
129 CREATE INDEX pg1_ix3 ON pg1(c); |
131 CREATE INDEX pg1_ix3 ON pg1(c); |
130 </codeblock> <p>And if you were to run a query like the following: </p> <codeblock id="GUID-A35663B7-4E7D-5CC0-BF5E-CF3A4CFED63F" xml:space="preserve"> |
132 </codeblock> <p>And if you were to run a query like the following: </p> <codeblock id="GUID-A35663B7-4E7D-5CC0-BF5E-CF3A4CFED63F" xml:space="preserve"> |
131 SELECT d FROM pg1 WHERE a=5 AND b=11 AND c=99; |
133 SELECT d FROM pg1 WHERE a=5 AND b=11 AND c=99; |
132 </codeblock> <p>Then PostgreSQL might attempt to optimize the query by using |
134 </codeblock> <p>Then PostgreSQL might attempt to optimize the query |
133 all three indexes, one for each term of the WHERE clause. </p> <p>SQLite does |
135 by using all three indexes, one for each term of the WHERE clause. </p> <p>SQLite does not work this way. SQLite is compelled to select |
134 not work this way. SQLite is compelled to select a single index to use in |
136 a single index to use in the query. It might select any of the three |
135 the query. It might select any of the three indexes shown, depending on which |
137 indexes shown, depending on which one the optimizer things will give |
136 one the optimizer things will give the best speedup. But in every case it |
138 the best speedup. But in every case it will only select a single index |
137 will only select a single index and only a single term of the WHERE clause |
139 and only a single term of the WHERE clause will be used. </p> <p>SQLite |
138 will be used. </p> <p>SQLite prefers to use a multi-column index such as this: </p> <codeblock id="GUID-40FB7075-1239-5089-BBC5-0D994F4A0C39" xml:space="preserve"> |
140 prefers to use a multi-column index such as this: </p> <codeblock id="GUID-40FB7075-1239-5089-BBC5-0D994F4A0C39" xml:space="preserve"> |
139 CREATE INDEX pg1_ix_all ON pg1(a,b,c); |
141 CREATE INDEX pg1_ix_all ON pg1(a,b,c); |
140 </codeblock> <p>If the pg1_ix_all index is available for use when the SELECT |
142 </codeblock> <p>If the pg1_ix_all index is available for use when |
141 statement above is prepared, SQLite will likely choose it over any of the |
143 the SELECT statement above is prepared, SQLite will likely choose |
142 single-column indexes because the multi-column index is able to make use of |
144 it over any of the single-column indexes because the multi-column |
143 all 3 terms of the WHERE clause. </p> <p>You can trick SQLite into using multiple |
145 index is able to make use of all 3 terms of the WHERE clause. </p> <p>You can trick SQLite into using multiple indexes on the same |
144 indexes on the same table by rewriting the query. Instead of the SELECT statement |
146 table by rewriting the query. Instead of the SELECT statement shown |
145 shown above, if you rewrite it as this: </p> <codeblock id="GUID-D7DE75D4-BB01-50DF-A9DC-956A83DED5D0" xml:space="preserve"> |
147 above, if you rewrite it as this: </p> <codeblock id="GUID-D7DE75D4-BB01-50DF-A9DC-956A83DED5D0" xml:space="preserve"> |
146 SELECT d FROM pg1 WHERE RowID IN ( |
148 SELECT d FROM pg1 WHERE RowID IN ( |
147 SELECT RowID FROM pg1 WHERE a=5 |
149 SELECT RowID FROM pg1 WHERE a=5 |
148 INTERSECT |
150 INTERSECT |
149 SELECT RowID FROM pg1 WHERE b=11 |
151 SELECT RowID FROM pg1 WHERE b=11 |
150 INTERSECT |
152 INTERSECT |
151 SELECT RowID FROM pg1 WHERE c=99 |
153 SELECT RowID FROM pg1 WHERE c=99 |
152 ) |
154 ) |
153 </codeblock> <p>Then each of the individual SELECT statements will using a |
155 </codeblock> <p>Then each of the individual SELECT statements will |
154 different single-column index and their results will be combined by the outer |
156 using a different single-column index and their results will be combined |
155 SELECT statement to give the correct result. The other SQL database engines |
157 by the outer SELECT statement to give the correct result. The other |
156 like PostgreSQL that are able to make use of multiple indexes per table do |
158 SQL database engines like PostgreSQL that are able to make use of |
157 so by treating the simpler SELECT statement shown first as if they where the |
159 multiple indexes per table do so by treating the simpler SELECT statement |
158 more complicated SELECT statement shown here. </p> </section> |
160 shown first as if they where the more complicated SELECT statement |
159 <section id="GUID-E90057A8-70B6-590C-B8AE-616DA25BB543"><title>Use Inequality |
161 shown here. </p> </section> |
160 Constraints on the Last Index Term</title> <p>Terms in the WHERE clause of |
162 <section id="GUID-E90057A8-70B6-590C-B8AE-616DA25BB543"><title>Use |
161 a query or UPDATE or DELETE statement are mostly likely to trigger the use |
163 Inequality Constraints on the Last Index Term</title> <p>Terms in |
162 of an index if they are an equality constraint – in other words if the term |
164 the WHERE clause of a query or UPDATE or DELETE statement are mostly |
163 consists of the name of an indexed column, an equal sign (“=”), and an expression. </p> <p>So, |
165 likely to trigger the use of an index if they are an equality constraint |
164 for example, if you have a table and index that look like this: </p> <codeblock id="GUID-84AADB9D-5853-57C2-B489-87DC7FB7AADE" xml:space="preserve"> |
166 – in other words if the term consists of the name of an indexed column, |
|
167 an equal sign (“=”), and an expression. </p> <p>So, for example, if |
|
168 you have a table and index that look like this: </p> <codeblock id="GUID-84AADB9D-5853-57C2-B489-87DC7FB7AADE" xml:space="preserve"> |
165 CREATE TABLE demo315(a,b,c,d); |
169 CREATE TABLE demo315(a,b,c,d); |
166 CREATE INDEX demo315_idx1 ON demo315(a,b,c); |
170 CREATE INDEX demo315_idx1 ON demo315(a,b,c); |
167 </codeblock> <p>And a query like this: </p> <codeblock id="GUID-A2B7DA9F-DB82-5D06-80E2-7AF714E403D5" xml:space="preserve"> |
171 </codeblock> <p>And a query like this: </p> <codeblock id="GUID-A2B7DA9F-DB82-5D06-80E2-7AF714E403D5" xml:space="preserve"> |
168 SELECT d FROM demo315 WHERE a=512; |
172 SELECT d FROM demo315 WHERE a=512; |
169 </codeblock> <p>The single “a=512” term of the WHERE clause qualifies as an |
173 </codeblock> <p>The single “a=512” term of the WHERE clause qualifies |
170 equality constraint and is likely to provoke the use of the demo315_idx1 index. </p> <p>SQLite |
174 as an equality constraint and is likely to provoke the use of the |
171 supports two other kinds of equality constraints. One is the IN operator: </p> <codeblock id="GUID-EA5D7637-A6B8-5BC0-A72E-D576B0F945A3" xml:space="preserve"> |
175 demo315_idx1 index. </p> <p>SQLite supports two other kinds of equality |
|
176 constraints. One is the IN operator: </p> <codeblock id="GUID-EA5D7637-A6B8-5BC0-A72E-D576B0F945A3" xml:space="preserve"> |
172 SELECT d FROM demo315 WHERE a IN (512,1024); |
177 SELECT d FROM demo315 WHERE a IN (512,1024); |
173 SELECT d FROM demo315 WHERE a IN (SELECT x FROM someothertable); |
178 SELECT d FROM demo315 WHERE a IN (SELECT x FROM someothertable); |
174 </codeblock> <p>There other is the IS NULL constraint: </p> <codeblock id="GUID-B2C1C84B-C33D-55C5-8484-24B28EFC8E37" xml:space="preserve"> |
179 </codeblock> <p>There other is the IS NULL constraint: </p> <codeblock id="GUID-B2C1C84B-C33D-55C5-8484-24B28EFC8E37" xml:space="preserve"> |
175 SELECT d FROM demo315 WHERE a IS NULL; |
180 SELECT d FROM demo315 WHERE a IS NULL; |
176 </codeblock> <p>SQLite allows at most one term of an index to be constrained |
181 </codeblock> <p>SQLite allows at most one term of an index to be constrained |
177 by an inequality such as less than “<”, greater than “>”, less than or |
182 by an inequality such as less than “<”, greater than “>”, less |
178 equal to “<=”, or greater than or equal to “>=”. </p> <p>The column that |
183 than or equal to “<=”, or greater than or equal to “>=”. </p> <p>The column that the inequality constrains will be the right-most |
179 the inequality constrains will be the right-most term of the index that is |
184 term of the index that is used. So, for example, in this query: </p> <codeblock id="GUID-563231B5-EC3A-57C2-BC6F-1A8129ADE308" xml:space="preserve"> |
180 used. So, for example, in this query: </p> <codeblock id="GUID-563231B5-EC3A-57C2-BC6F-1A8129ADE308" xml:space="preserve"> |
|
181 SELECT d FROM demo315 WHERE a=5 AND b>11 AND c=1; |
185 SELECT d FROM demo315 WHERE a=5 AND b>11 AND c=1; |
182 </codeblock> <p>Only the first two terms of the WHERE clause will be used |
186 </codeblock> <p>Only the first two terms of the WHERE clause will |
183 with the demo315_idx1 index. The third term, the “c=1” constraint, cannot |
187 be used with the demo315_idx1 index. The third term, the “c=1” constraint, |
184 be used because the “c” column occurs to the right of the “b” column in the |
188 cannot be used because the “c” column occurs to the right of the “b” |
185 index and the “b” column is constrained by an inequality. </p> <p>SQLite allows |
189 column in the index and the “b” column is constrained by an inequality. </p> <p>SQLite allows up to two inequalities on the same column as long |
186 up to two inequalities on the same column as long as the two inequalities |
190 as the two inequalities provide an upper and lower bound on the column. |
187 provide an upper and lower bound on the column. For example, in this query: </p> <codeblock id="GUID-4EB94886-EDFF-58F2-8692-011A67AC5A60" xml:space="preserve"> |
191 For example, in this query: </p> <codeblock id="GUID-4EB94886-EDFF-58F2-8692-011A67AC5A60" xml:space="preserve"> |
188 SELECT d FROM demo315 WHERE a=5 AND b>11 AND b<23; |
192 SELECT d FROM demo315 WHERE a=5 AND b>11 AND b<23; |
189 </codeblock> <p>All three terms of the WHERE clause will be used because the |
193 </codeblock> <p>All three terms of the WHERE clause will be used because |
190 two inequalities on the “b” column provide an upper and lower bound on the |
194 the two inequalities on the “b” column provide an upper and lower |
191 value of “b”. </p> <p>SQLite will only use the four inequalities mentioned |
195 bound on the value of “b”. </p> <p>SQLite will only use the four inequalities |
192 above to help constrain a search: “<”, “>”, “<=”, and “>=”. Other inequality |
196 mentioned above to help constrain a search: “<”, “>”, “<=”, |
193 operators such as not equal to (“!=” or “<>”) and NOT NULL are not helpful |
197 and “>=”. Other inequality operators such as not equal to (“!=” or |
194 to the query optimizer and will never be used to control an index and help |
198 “<>”) and NOT NULL are not helpful to the query optimizer and will |
195 make the query run faster. </p> </section> |
199 never be used to control an index and help make the query run faster. </p> </section> |
196 <section id="GUID-CAD0C181-37E7-578A-A7E1-7843447C247F"><title>Use Indexes |
200 <section id="GUID-CAD0C181-37E7-578A-A7E1-7843447C247F"><title>Use |
197 To Help ORDER BY Clauses Evaluate Faster</title> <p>The default method for |
201 Indexes To Help ORDER BY Clauses Evaluate Faster</title> <p>The default |
198 evaluating an ORDER BY clause in a SELECT statement is to first evaluate the |
202 method for evaluating an ORDER BY clause in a SELECT statement is |
199 SELECT statement and store the results in a temporary tables, then sort the |
203 to first evaluate the SELECT statement and store the results in a |
200 temporary table according to the ORDER BY clause and scan the sorted temporary |
204 temporary tables, then sort the temporary table according to the ORDER |
201 table to generate the final output. </p> <p>This method always works, but |
205 BY clause and scan the sorted temporary table to generate the final |
202 it requires three passes over the data (one pass to generate the result set, |
206 output. </p> <p>This method always works, but it requires three passes |
203 a second pass to sort the result set, and a third pass to output the results) |
207 over the data (one pass to generate the result set, a second pass |
204 and it requires a temporary storage space sufficiently large to contain the |
208 to sort the result set, and a third pass to output the results) and |
205 entire results set. </p> <p>Where possible, SQLite will avoid storing and |
209 it requires a temporary storage space sufficiently large to contain |
206 sorting the result set by using an index that causes the results to emerge |
210 the entire results set. </p> <p>Where possible, SQLite will avoid |
207 from the query in sorted order in the first place. </p> <p>The way to get |
211 storing and sorting the result set by using an index that causes the |
208 SQLite to use an index for sorting is to provide an index that covers the |
212 results to emerge from the query in sorted order in the first place. </p> <p>The way to get SQLite to use an index for sorting is to provide |
209 same columns specified in the ORDER BY clause. For example, if the table and |
213 an index that covers the same columns specified in the ORDER BY clause. |
210 index are like this: </p> <codeblock id="GUID-F0103033-C5C8-5177-8AD7-70BCC45C33C9" xml:space="preserve"> |
214 For example, if the table and index are like this: </p> <codeblock id="GUID-F0103033-C5C8-5177-8AD7-70BCC45C33C9" xml:space="preserve"> |
211 CREATE TABLE demo316(a,b,c,data); |
215 CREATE TABLE demo316(a,b,c,data); |
212 CREATE INDEX idx316 ON demo316(a,b,c); |
216 CREATE INDEX idx316 ON demo316(a,b,c); |
213 </codeblock> <p>And you do a query like this: </p> <codeblock id="GUID-D67BB6FF-E213-5B86-A2C1-E1992DA96A62" xml:space="preserve"> |
217 </codeblock> <p>And you do a query like this: </p> <codeblock id="GUID-D67BB6FF-E213-5B86-A2C1-E1992DA96A62" xml:space="preserve"> |
214 SELECT data FROM demo316 ORDER BY a,b,c; |
218 SELECT data FROM demo316 ORDER BY a,b,c; |
215 </codeblock> <p>SQLite will use the idx316 index to implement the ORDER BY |
219 </codeblock> <p>SQLite will use the idx316 index to implement the |
216 clause, obviating the need for temporary storage space and a separate sorting |
220 ORDER BY clause, obviating the need for temporary storage space and |
217 pass. </p> <p>An index can be used to satisfy the search constraints of a |
221 a separate sorting pass. </p> <p>An index can be used to satisfy the |
218 WHERE clause and to impose the ORDER BY ordering of outputs all at once. The |
222 search constraints of a WHERE clause and to impose the ORDER BY ordering |
219 trick is for the ORDER BY clause terms to occur immediately after the WHERE |
223 of outputs all at once. The trick is for the ORDER BY clause terms |
220 clause terms in the index. For example, one can write: </p> <codeblock id="GUID-02063968-34B5-5766-9D02-86D696D39C1E" xml:space="preserve"> |
224 to occur immediately after the WHERE clause terms in the index. For |
|
225 example, one can write: </p> <codeblock id="GUID-02063968-34B5-5766-9D02-86D696D39C1E" xml:space="preserve"> |
221 SELECT data FROM demo316 WHERE a=5 ORDER BY b,c; |
226 SELECT data FROM demo316 WHERE a=5 ORDER BY b,c; |
222 </codeblock> <p>The “a” column is used in the WHERE clause and the immediately |
227 </codeblock> <p>The “a” column is used in the WHERE clause and the |
223 following terms of the index, “b” and “c” are used in the ORDER BY clause. |
228 immediately following terms of the index, “b” and “c” are used in |
224 So in this case the idx316 index would be used both to speed up the search |
229 the ORDER BY clause. So in this case the idx316 index would be used |
225 and to satisfy the ORDER BY clause. </p> <p>This query also uses the idx316 |
230 both to speed up the search and to satisfy the ORDER BY clause. </p> <p>This query also uses the idx316 index because, once again, the |
226 index because, once again, the ORDER BY clause term “c” immediate follows |
231 ORDER BY clause term “c” immediate follows the WHERE clause terms |
227 the WHERE clause terms “a” and “b” in the index: </p> <codeblock id="GUID-6760EC7E-E86A-5EBD-BDDD-32A68BE78A9E" xml:space="preserve"> |
232 “a” and “b” in the index: </p> <codeblock id="GUID-6760EC7E-E86A-5EBD-BDDD-32A68BE78A9E" xml:space="preserve"> |
228 SELECT data FROM demo316 WHERE a=5 AND b=17 ORDER BY c; |
233 SELECT data FROM demo316 WHERE a=5 AND b=17 ORDER BY c; |
229 </codeblock> <p>But now consider this: </p> <codeblock id="GUID-9363996C-8C30-5E04-B05F-392C8262F1F6" xml:space="preserve"> |
234 </codeblock> <p>But now consider this: </p> <codeblock id="GUID-9363996C-8C30-5E04-B05F-392C8262F1F6" xml:space="preserve"> |
230 SELECT data FROM demo316 WHERE a=5 ORDER BY c; |
235 SELECT data FROM demo316 WHERE a=5 ORDER BY c; |
231 </codeblock> <p>Here there is a gap between the ORDER BY term “c” and the |
236 </codeblock> <p>Here there is a gap between the ORDER BY term “c” |
232 WHERE clause term “a”. So the idx316 index cannot be used to satisfy both |
237 and the WHERE clause term “a”. So the idx316 index cannot be used |
233 the WHERE clause and the ORDER BY clause. The index will be used on the WHERE |
238 to satisfy both the WHERE clause and the ORDER BY clause. The index |
234 clause and a separate sorting pass will occur to put the results in the correct |
239 will be used on the WHERE clause and a separate sorting pass will |
235 order. </p> </section> |
240 occur to put the results in the correct order. </p> </section> |
236 <section id="GUID-109AF0DA-A054-504A-A432-76BD145B2AC4"><title>Add Result |
241 <section id="GUID-109AF0DA-A054-504A-A432-76BD145B2AC4"><title>Add |
237 Columns To The End Of Indexes</title> <p>Queries will sometimes run faster |
242 Result Columns To The End Of Indexes</title> <p>Queries will sometimes |
238 if their result columns appear in the right-most entries of an index. Consider |
243 run faster if their result columns appear in the right-most entries |
239 the following example: </p> <codeblock id="GUID-63292052-B523-5671-B3EE-E10A66C7275F" xml:space="preserve"> |
244 of an index. Consider the following example: </p> <codeblock id="GUID-63292052-B523-5671-B3EE-E10A66C7275F" xml:space="preserve"> |
240 CREATE TABLE demo317(a,b,c,data); |
245 CREATE TABLE demo317(a,b,c,data); |
241 CREATE INDEX idx317 ON demo316(a,b,c); |
246 CREATE INDEX idx317 ON demo316(a,b,c); |
242 </codeblock> <p>A query where all result column terms appears in the index, |
247 </codeblock> <p>A query where all result column terms appears in the |
243 such as </p> <codeblock id="GUID-41F740E7-EAFC-583B-BFE6-E63DBEA354D7" xml:space="preserve"> |
248 index, such as </p> <codeblock id="GUID-41F740E7-EAFC-583B-BFE6-E63DBEA354D7" xml:space="preserve"> |
244 SELECT c FROM demo317 WHERE a=5 ORDER BY b; |
249 SELECT c FROM demo317 WHERE a=5 ORDER BY b; |
245 </codeblock> <p>will typically run about twice as fast or faster than a query |
250 </codeblock> <p>will typically run about twice as fast or faster than |
246 that uses columns that are not in the index, e.g. </p> <codeblock id="GUID-098752F4-304A-5A84-834E-240D97D97C2D" xml:space="preserve"> |
251 a query that uses columns that are not in the index, e.g. </p> <codeblock id="GUID-098752F4-304A-5A84-834E-240D97D97C2D" xml:space="preserve"> |
247 SELECT data FROM demo317 WHERE a=5 ORDER BY b; |
252 SELECT data FROM demo317 WHERE a=5 ORDER BY b; |
248 </codeblock> <p>The reason for this is that when all information is contained |
253 </codeblock> <p>The reason for this is that when all information is |
249 within the index entry only a single search has to be made for each row of |
254 contained within the index entry only a single search has to be made |
250 output. But when some of the information is in the index and other parts are |
255 for each row of output. But when some of the information is in the |
251 in the table, first there must be a search for the appropriate index entry |
256 index and other parts are in the table, first there must be a search |
252 then a separate search is made for the appropriate table row based on the |
257 for the appropriate index entry then a separate search is made for |
253 RowID found in the index entry. Twice as much searching has to be done for |
258 the appropriate table row based on the RowID found in the index entry. |
254 each row of output generated. </p> <p>The extra query speed does not come |
259 Twice as much searching has to be done for each row of output generated. </p> <p>The extra query speed does not come for free, however. Adding |
255 for free, however. Adding additional columns to an index makes the database |
260 additional columns to an index makes the database file larger. So |
256 file larger. So when developing an application, the programmer will need to |
261 when developing an application, the programmer will need to make a |
257 make a space versus time trade-off to determine whether the extra columns |
262 space versus time trade-off to determine whether the extra columns |
258 should be added to the index or not. </p> <p>Note that if any column of the |
263 should be added to the index or not. </p> <p>Note that if any column |
259 result must be obtained from the original table, then the table row will have |
264 of the result must be obtained from the original table, then the table |
260 to be searched for anyhow. There will be no speed advantage, so you might |
265 row will have to be searched for anyhow. There will be no speed advantage, |
261 as well omit the extra columns from the end of the index and save on storage |
266 so you might as well omit the extra columns from the end of the index |
262 space. The speed-up described in this section can only be realized when every |
267 and save on storage space. The speed-up described in this section |
263 column in a table is obtainable from the index. </p> <p>Taking into account |
268 can only be realized when every column in a table is obtainable from |
264 the results of the previous few sections, the best set of columns to put in |
269 the index. </p> <p>Taking into account the results of the previous |
265 an index can be described as follows: </p> <ul> |
270 few sections, the best set of columns to put in an index can be described |
266 <li id="GUID-EBF4DEFB-2F5F-5D78-92FA-06FEAB0C3650"><p>The first columns in |
271 as follows: </p> <ul> |
267 the index should be columns that have equality constraints in the WHERE clause |
272 <li id="GUID-EBF4DEFB-2F5F-5D78-92FA-06FEAB0C3650"><p>The first columns |
268 of the query. </p> </li> |
273 in the index should be columns that have equality constraints in the |
269 <li id="GUID-E5CB725C-6304-5946-9E18-E69B5F1A6A88"><p>The second group of |
274 WHERE clause of the query. </p> </li> |
270 columns should match the columns specified in the ORDER BY clause. </p> </li> |
275 <li id="GUID-E5CB725C-6304-5946-9E18-E69B5F1A6A88"><p>The second group |
271 <li id="GUID-FBC00251-C3AD-5AC0-9102-EF66EA37DE4E"><p>Add additional columns |
276 of columns should match the columns specified in the ORDER BY clause. </p> </li> |
272 to the end of the index that are used in the result set of the query. </p> </li> |
277 <li id="GUID-FBC00251-C3AD-5AC0-9102-EF66EA37DE4E"><p>Add additional |
|
278 columns to the end of the index that are used in the result set of |
|
279 the query. </p> </li> |
273 </ul> </section> |
280 </ul> </section> |
274 <section id="GUID-D7B5B389-E031-5512-8186-235B22F0D9C1"><title>Resolve Indexing |
281 <section id="GUID-D7B5B389-E031-5512-8186-235B22F0D9C1"><title>Resolve |
275 Ambiguities Using the Unary “+” Operator</title> <p>The SQLite query optimizer |
282 Indexing Ambiguities Using the Unary “+” Operator</title> <p>The SQLite |
276 usually does a good job of choosing the best index to use for a particular |
283 query optimizer usually does a good job of choosing the best index |
277 query, especially if ANALYZE has been run to provide it with index performance |
284 to use for a particular query, especially if ANALYZE has been run |
278 statistics. But occasions do arise where it is useful to give the optimizer |
285 to provide it with index performance statistics. But occasions do |
279 hints. </p> <p>One of the easiest ways to control the operation of the optimizer |
286 arise where it is useful to give the optimizer hints. </p> <p>One |
280 is to disqualify terms in the WHERE clause or ORDER BY clause as candidates |
287 of the easiest ways to control the operation of the optimizer is to |
281 for optimization by using the unary “+” operator. </p> <p>In SQLite, a unary |
288 disqualify terms in the WHERE clause or ORDER BY clause as candidates |
282 “+” operator is a no-op. It makes no change to its operand, even if the operand |
289 for optimization by using the unary “+” operator. </p> <p>In SQLite, |
283 is something other than a number. So you can always prefix a “+” to an expression |
290 a unary “+” operator is a no-op. It makes no change to its operand, |
284 in without changing the meaning of the expression. As the optimizer will only |
291 even if the operand is something other than a number. So you can always |
285 use terms in WHERE, HAVING, or ON clauses that have an index column name on |
292 prefix a “+” to an expression in without changing the meaning of the |
286 one side of a comparison operator, you can prevent such a term from being |
293 expression. As the optimizer will only use terms in WHERE, HAVING, |
287 used by the optimizer by prefixing the column name with a “+”. </p> <p>For |
294 or ON clauses that have an index column name on one side of a comparison |
288 example, suppose you have a database with a schema like this: </p> <codeblock id="GUID-E7747EFD-FE58-5EA4-88B3-097C0A303F52" xml:space="preserve"> |
295 operator, you can prevent such a term from being used by the optimizer |
|
296 by prefixing the column name with a “+”. </p> <p>For example, suppose |
|
297 you have a database with a schema like this: </p> <codeblock id="GUID-E7747EFD-FE58-5EA4-88B3-097C0A303F52" xml:space="preserve"> |
289 CREATE TABLE demo321(a,b,c,data); |
298 CREATE TABLE demo321(a,b,c,data); |
290 CREATE INDEX idx321a ON demo321(a); |
299 CREATE INDEX idx321a ON demo321(a); |
291 CREATE INDEX idx321b ON demo321(b); |
300 CREATE INDEX idx321b ON demo321(b); |
292 </codeblock> <p>If you issue a query such as this: </p> <codeblock id="GUID-87BD59FC-33A8-598B-B91F-607B26F7349D" xml:space="preserve"> |
301 </codeblock> <p>If you issue a query such as this: </p> <codeblock id="GUID-87BD59FC-33A8-598B-B91F-607B26F7349D" xml:space="preserve"> |
293 SELECT data FROM demo321 WHERE a=5 AND b=11; |
302 SELECT data FROM demo321 WHERE a=5 AND b=11; |
294 </codeblock> <p>The query optimizer might use the “a=5” term with idx321a |
303 </codeblock> <p>The query optimizer might use the “a=5” term with |
295 or it might use the “b=11” term with the idx321b index. But if you want to |
304 idx321a or it might use the “b=11” term with the idx321b index. But |
296 force the use of the idx321a index you can accomplish that by disqualifying |
305 if you want to force the use of the idx321a index you can accomplish |
297 the second term of the WHERE clause as a candidate for optimization using |
306 that by disqualifying the second term of the WHERE clause as a candidate |
298 a unary “+” like this: </p> <codeblock id="GUID-E6EAB459-726A-5FE4-8065-6C46AC2C5B5C" xml:space="preserve"> |
307 for optimization using a unary “+” like this: </p> <codeblock id="GUID-E6EAB459-726A-5FE4-8065-6C46AC2C5B5C" xml:space="preserve"> |
299 SELECT data FROM demo321 WHERE a=5 AND +b=11; |
308 SELECT data FROM demo321 WHERE a=5 AND +b=11; |
300 </codeblock> <p>The “+” in front of the “b=11” turns the left-hand side of |
309 </codeblock> <p>The “+” in front of the “b=11” turns the left-hand |
301 the equals comparison operator into an expression instead of an indexed column |
310 side of the equals comparison operator into an expression instead |
302 name. The optimizer will then not recognize that the second term can be used |
311 of an indexed column name. The optimizer will then not recognize that |
303 with an index and so the optimizer is compelled to use the first “a=5” term. </p> <p>The |
312 the second term can be used with an index and so the optimizer is |
304 unary “+” operator can also be used to disable ORDER BY clause optimizations. |
313 compelled to use the first “a=5” term. </p> <p>The unary “+” operator |
305 Consider this query: </p> <codeblock id="GUID-0488D466-77B7-50E0-AB85-FF033A2D75DC" xml:space="preserve"> |
314 can also be used to disable ORDER BY clause optimizations. Consider |
|
315 this query: </p> <codeblock id="GUID-0488D466-77B7-50E0-AB85-FF033A2D75DC" xml:space="preserve"> |
306 SELECT data FROM demo321 WHERE a=5 ORDER BY b; |
316 SELECT data FROM demo321 WHERE a=5 ORDER BY b; |
307 </codeblock> <p>The optimizer has the choice of using the “a=5” term of the |
317 </codeblock> <p>The optimizer has the choice of using the “a=5” term |
308 WHERE clause with idx321a to restrict the search. Or it might choose to use |
318 of the WHERE clause with idx321a to restrict the search. Or it might |
309 do a full table scan with idx321b to satisfy the ORDER BY clause and thus |
319 choose to use do a full table scan with idx321b to satisfy the ORDER |
310 avoid a separate sorting pass. You can force one choice or the other using |
320 BY clause and thus avoid a separate sorting pass. You can force one |
311 a unary “+”. </p> <p>To force the use of idx321a on the WHERE clause, add |
321 choice or the other using a unary “+”. </p> <p>To force the use of |
312 the unary “+” in from of the “b” in the ORDER BY clause: </p> <codeblock id="GUID-E55A085F-D91F-58E0-B964-317BB3A9D7ED" xml:space="preserve"> |
322 idx321a on the WHERE clause, add the unary “+” in from of the “b” |
|
323 in the ORDER BY clause: </p> <codeblock id="GUID-E55A085F-D91F-58E0-B964-317BB3A9D7ED" xml:space="preserve"> |
313 SELECT data FROM demo321 WHERE a=5 ORDER BY +b; |
324 SELECT data FROM demo321 WHERE a=5 ORDER BY +b; |
314 </codeblock> <p>To go the other way and force the idx321b index to be used |
325 </codeblock> <p>To go the other way and force the idx321b index to |
315 to satisfy the ORDER BY clause, disqualify the WHERE term by prefixing with |
326 be used to satisfy the ORDER BY clause, disqualify the WHERE term |
316 a unary “+”: </p> <codeblock id="GUID-D97EF52A-1F74-57EB-AC11-7911B4E088B3" xml:space="preserve"> |
327 by prefixing with a unary “+”: </p> <codeblock id="GUID-D97EF52A-1F74-57EB-AC11-7911B4E088B3" xml:space="preserve"> |
317 SELECT data FROM demo321 WHERE +a=5 ORDER BY b; |
328 SELECT data FROM demo321 WHERE +a=5 ORDER BY b; |
318 </codeblock> <p>The reader is cautioned not to overuse the unary “+” operator. |
329 </codeblock> <p>The reader is cautioned not to overuse the unary “+” |
319 The SQLite query optimizer usually picks the best index without any outside |
330 operator. The SQLite query optimizer usually picks the best index |
320 help. Premature use of unary “+” can confuse the optimizer and cause less |
331 without any outside help. Premature use of unary “+” can confuse the |
321 than optimal performance. But in some cases it is useful to be able override |
332 optimizer and cause less than optimal performance. But in some cases |
322 the decisions of the optimizer, and the unary “+” operator is an excellent |
333 it is useful to be able override the decisions of the optimizer, and |
323 way to do this when it becomes necessary. </p> </section> |
334 the unary “+” operator is an excellent way to do this when it becomes |
324 <section id="GUID-7BEBC49C-0528-5D58-9626-2A92F3D0D9E8"><title>Avoid Indexing |
335 necessary. </p> </section> |
325 Large BLOBs and CLOBs</title> <p>SQLite stores indexes as b-trees. Each b-tree |
336 <section id="GUID-7BEBC49C-0528-5D58-9626-2A92F3D0D9E8"><title>Avoid |
326 node uses one page of the database file. In order to maintain an acceptable |
337 Indexing Large BLOBs and CLOBs</title> <p>SQLite stores indexes as |
327 fan-out, the b-tree module within SQLite requires that at least 4 entries |
338 b-trees. Each b-tree node uses one page of the database file. In order |
328 must fit on each page of a b-tree. There is also some overhead associated |
339 to maintain an acceptable fan-out, the b-tree module within SQLite |
329 with each b-tree page. So at the most there is about 250 bytes of space available |
340 requires that at least 4 entries must fit on each page of a b-tree. |
330 on the main b-tree page for each index entry. </p> <p>If an index entry exceeds |
341 There is also some overhead associated with each b-tree page. So at |
331 this allotment of approximately 250 bytes excess bytes are spilled to overflow |
342 the most there is about 250 bytes of space available on the main b-tree |
332 pages. There is no arbitrary limit on the number of overflow pages or on the |
343 page for each index entry. </p> <p>If an index entry exceeds this |
333 length of a b-tree entry, but for maximum efficiency it is best to avoid overflow |
344 allotment of approximately 250 bytes excess bytes are spilled to overflow |
334 pages, especially in indexes. This means that you should strive to keep the |
345 pages. There is no arbitrary limit on the number of overflow pages |
335 number of bytes in each index entry below 250. </p> <p>If you keep the size |
346 or on the length of a b-tree entry, but for maximum efficiency it |
336 of indexes significantly smaller than 250 bytes, then the b-tree fan-out is |
347 is best to avoid overflow pages, especially in indexes. This means |
337 increased and the binary search algorithm used to search for entries in an |
348 that you should strive to keep the number of bytes in each index entry |
338 index has fewer pages to examine and therefore runs faster. So the fewer bytes |
349 below 250. </p> <p>If you keep the size of indexes significantly smaller |
339 used in each index entry the better, at least from a performance perspective. </p> <p>For |
350 than 250 bytes, then the b-tree fan-out is increased and the binary |
340 these reasons, it is recommended that you avoid indexing large BLOBs and CLOBs. |
351 search algorithm used to search for entries in an index has fewer |
341 SQLite will continue to work when large BLOBs and CLOBs are indexed, but there |
352 pages to examine and therefore runs faster. So the fewer bytes used |
342 will be a performance impact. </p> <p>On the other hand, if you need to lookup |
353 in each index entry the better, at least from a performance perspective. </p> <p>For these reasons, it is recommended that you avoid indexing |
343 entries using a large BLOB or CLOB as the key, then by all means use an index. |
354 large BLOBs and CLOBs. SQLite will continue to work when large BLOBs |
344 An index on a large BLOB or CLOB is not as fast as an index using more compact |
355 and CLOBs are indexed, but there will be a performance impact. </p> <p>On the other hand, if you need to lookup entries using a large |
345 data types such as integers, but it is still many order of magnitude faster |
356 BLOB or CLOB as the key, then by all means use an index. An index |
346 than doing a full table scan. So to be more precise, the advice of this section |
357 on a large BLOB or CLOB is not as fast as an index using more compact |
347 is that you should design your applications so that you do not need to lookup |
358 data types such as integers, but it is still many order of magnitude |
348 entries using a large BLOB or CLOB as the key. Try to arrange to have compact |
359 faster than doing a full table scan. So to be more precise, the advice |
349 keys consisting of short strings or integers. </p> <p>Note that many other |
360 of this section is that you should design your applications so that |
350 SQL database engines disallow the indexing of BLOBs and CLOBs in the first |
361 you do not need to lookup entries using a large BLOB or CLOB as the |
351 place. You simple cannot do it. SQLite is more flexible that most in that |
362 key. Try to arrange to have compact keys consisting of short strings |
352 it does allow BLOBs and CLOBs to be indexed and it will use those indexes |
363 or integers. </p> <p>Note that many other SQL database engines disallow |
353 when appropriate. But for maximum performance, it is best to use smaller search |
364 the indexing of BLOBs and CLOBs in the first place. You simple cannot |
354 keys. </p> </section> |
365 do it. SQLite is more flexible that most in that it does allow BLOBs |
355 <section id="GUID-DD40F29F-DF93-536E-9B52-F9B9FF45155D"><title>Avoid Excess |
366 and CLOBs to be indexed and it will use those indexes when appropriate. |
356 Indexes</title> <p>Some developers approach SQL-based application development |
367 But for maximum performance, it is best to use smaller search keys. </p> </section> |
357 with the attitude that indexes never hurt and that the more indexes you have, |
368 <section id="GUID-DD40F29F-DF93-536E-9B52-F9B9FF45155D"><title>Avoid |
358 the faster your application will run. This is definitely not the case. There |
369 Excess Indexes</title> <p>Some developers approach SQL-based application |
359 is a costs associated with each new index you create: </p> <ul> |
370 development with the attitude that indexes never hurt and that the |
360 <li id="GUID-FD257BF7-F938-54B5-AC03-9536712D6281"><p>Each new index takes |
371 more indexes you have, the faster your application will run. This |
361 up additional space in the database file. The more indexes you have, the larger |
372 is definitely not the case. There is a costs associated with each |
362 your database files will become for the same amount of data. </p> </li> |
373 new index you create: </p> <ul> |
363 <li id="GUID-E1B74FB6-246A-5148-AF06-04E1B4B949F1"><p>Every INSERT and UPDATE |
374 <li id="GUID-FD257BF7-F938-54B5-AC03-9536712D6281"><p>Each new index |
364 statement modifies both the original table and all indexes on that table. |
375 takes up additional space in the database file. The more indexes you |
365 So the performance of INSERT and UPDATE decreases linearly with the number |
376 have, the larger your database files will become for the same amount |
366 of indexes. </p> </li> |
377 of data. </p> </li> |
367 <li id="GUID-56AAE2D1-71D6-5A23-8190-B0C80B204DED"><p>Compiling new SQL statements |
378 <li id="GUID-E1B74FB6-246A-5148-AF06-04E1B4B949F1"><p>Every INSERT |
368 using <codeph>Prepare()</codeph> takes longer when there are more indexes |
379 and UPDATE statement modifies both the original table and all indexes |
369 for the optimizer to choose between. </p> </li> |
380 on that table. So the performance of INSERT and UPDATE decreases linearly |
370 <li id="GUID-24B7F7D8-FAA9-5C78-B3C7-B886FA774C0B"><p>Surplus indexes give |
381 with the number of indexes. </p> </li> |
371 the optimizer more opportunities to make a bad choice. </p> </li> |
382 <li id="GUID-56AAE2D1-71D6-5A23-8190-B0C80B204DED"><p>Compiling new |
372 </ul> <p>Your policy on indexes should be to avoid them wherever you can. |
383 SQL statements using <codeph>Prepare()</codeph> takes longer when |
373 Indexes are powerful medicine and can work wonders to improve the performance |
384 there are more indexes for the optimizer to choose between. </p> </li> |
374 of a program. But just as too many drugs can be worse than none at all, so |
385 <li id="GUID-24B7F7D8-FAA9-5C78-B3C7-B886FA774C0B"><p>Surplus indexes |
375 also can too many indexes cause more harm than good. </p> <p>When building |
386 give the optimizer more opportunities to make a bad choice. </p> </li> |
376 a new application, a good approach is to omit all explicitly declared indexes |
387 </ul> <p>Your policy on indexes should be to avoid them wherever you |
377 in the beginning and only add indexes as needed to address specific performance |
388 can. Indexes are powerful medicine and can work wonders to improve |
378 problems. </p> <p>Take care to avoid redundant indexes. For example, consider |
389 the performance of a program. But just as too many drugs can be worse |
379 this schema: </p> <codeblock id="GUID-89F20101-1628-5783-82B0-2ABE84078C7D" xml:space="preserve"> |
390 than none at all, so also can too many indexes cause more harm than |
|
391 good. </p> <p>When building a new application, a good approach is |
|
392 to omit all explicitly declared indexes in the beginning and only |
|
393 add indexes as needed to address specific performance problems. </p> <p>Take care to avoid redundant indexes. For example, consider this |
|
394 schema: </p> <codeblock id="GUID-89F20101-1628-5783-82B0-2ABE84078C7D" xml:space="preserve"> |
380 CREATE TABLE demo323a(a,b,c); |
395 CREATE TABLE demo323a(a,b,c); |
381 CREATE INDEX idx323a1 ON demo323(a); |
396 CREATE INDEX idx323a1 ON demo323(a); |
382 CREATE INDEX idx323a2 ON demo323(a,b); |
397 CREATE INDEX idx323a2 ON demo323(a,b); |
383 </codeblock> <p>The idx323a1 index is redundant and can be eliminated. Anything |
398 </codeblock> <p>The idx323a1 index is redundant and can be eliminated. |
384 that the idx323a1 index can do the idx323a2 index can do better. </p> <p>Other |
399 Anything that the idx323a1 index can do the idx323a2 index can do |
385 redundancies are not quite as apparent as the above. Recall that any column |
400 better. </p> <p>Other redundancies are not quite as apparent as the |
386 or columns that are declared UNIQUE or PRIMARY KEY (except for the special |
401 above. Recall that any column or columns that are declared UNIQUE |
387 case of INTEGER PRIMARY KEY) are automatically indexed. So in the following |
402 or PRIMARY KEY (except for the special case of INTEGER PRIMARY KEY) |
388 schema: </p> <codeblock id="GUID-2FE7B726-4027-518C-9217-B4BD1ECDA991" xml:space="preserve"> |
403 are automatically indexed. So in the following schema: </p> <codeblock id="GUID-2FE7B726-4027-518C-9217-B4BD1ECDA991" xml:space="preserve"> |
389 CREATE TABLE demo323b(x TEXT PRIMARY KEY, y INTEGER UNIQUE); |
404 CREATE TABLE demo323b(x TEXT PRIMARY KEY, y INTEGER UNIQUE); |
390 CREATE INDEX idx323b1 ON demo323b(x); |
405 CREATE INDEX idx323b1 ON demo323b(x); |
391 CREATE INDEX idx323b2 ON demo323b(y); |
406 CREATE INDEX idx323b2 ON demo323b(y); |
392 </codeblock> <p>Both indexes are redundant and can be eliminated with no loss |
407 </codeblock> <p>Both indexes are redundant and can be eliminated with |
393 in query performance. Occasionally one sees a novice SQL programmer use both |
408 no loss in query performance. Occasionally one sees a novice SQL programmer |
394 UNIQUE and PRIMARY KEY on the same column: </p> <codeblock id="GUID-CDE12649-BDB4-58D4-8981-02628BDF5C79" xml:space="preserve"> |
409 use both UNIQUE and PRIMARY KEY on the same column: </p> <codeblock id="GUID-CDE12649-BDB4-58D4-8981-02628BDF5C79" xml:space="preserve"> |
395 CREATE TABLE demo323c(p TEXT UNIQUE PRIMARY KEY, q); |
410 CREATE TABLE demo323c(p TEXT UNIQUE PRIMARY KEY, q); |
396 </codeblock> <p>This has the effect of creating two indexes on the “p” column |
411 </codeblock> <p>This has the effect of creating two indexes on the |
397 – one for the UNIQUE keywords and another for the PRIMARY KEY keyword. Both |
412 “p” column – one for the UNIQUE keywords and another for the PRIMARY |
398 indexes are identical so clearly one can be omitted. A PRIMARY KEY is guaranteed |
413 KEY keyword. Both indexes are identical so clearly one can be omitted. |
399 to always be unique so the UNIQUE keyword can be removed from the demo323c |
414 A PRIMARY KEY is guaranteed to always be unique so the UNIQUE keyword |
400 table definition with no ambiguity or loss of functionality. </p> <p>It is |
415 can be removed from the demo323c table definition with no ambiguity |
401 not a fatal error to create too many indexes or redundant indexes. SQLite |
416 or loss of functionality. </p> <p>It is not a fatal error to create |
402 will continue to generate the correct answers but it may take longer to produce |
417 too many indexes or redundant indexes. SQLite will continue to generate |
403 those answers and the resulting database files might be a little larger. So |
418 the correct answers but it may take longer to produce those answers |
404 for best results, keep the number of indexes to a minimum. </p> </section> |
419 and the resulting database files might be a little larger. So for |
405 <section id="GUID-9337E315-BB5A-56D0-8319-6C398D26151F"><title>Avoid Tables |
420 best results, keep the number of indexes to a minimum. </p> </section> |
406 and Indexes with an Excessive Number of Columns</title> <p>SQLite places no |
421 <section id="GUID-9337E315-BB5A-56D0-8319-6C398D26151F"><title>Avoid |
407 arbitrary limits on the number of columns in a table or index. There are known |
422 Tables and Indexes with an Excessive Number of Columns</title> <p>SQLite places no arbitrary limits on the number of columns in a table |
408 commercial applications using SQLite that construct tables with tens of thousands |
423 or index. There are known commercial applications using SQLite that |
409 of columns each. And these applications actually work. </p> <p>However the |
424 construct tables with tens of thousands of columns each. And these |
410 database engine is optimized for the common case of tables with no more than |
425 applications actually work. </p> <p>However the database engine is |
411 a few dozen columns. For best performance you should try to stay in the optimized |
426 optimized for the common case of tables with no more than a few dozen |
412 region. Furthermore, we note that relational databases with a large number |
427 columns. For best performance you should try to stay in the optimized |
413 of columns are usually not well normalized. So even apart from performance |
428 region. Furthermore, we note that relational databases with a large |
414 considerations, if you find your design has tables with more than a dozen |
429 number of columns are usually not well normalized. So even apart from |
415 or so columns, you really need to rethink how you are building your application. </p> <p>There |
430 performance considerations, if you find your design has tables with |
416 are a number of places in <codeph>Prepare()</codeph> that run in time O(N<sup>2</sup>) |
431 more than a dozen or so columns, you really need to rethink how you |
417 where N is the number of columns in the table. The constant of proportionality |
432 are building your application. </p> <p>There are a number of places |
418 is small in these cases so you should not have any problems for N of less |
433 in <codeph>Prepare()</codeph> that run in time O(N<sup>2</sup>) where |
419 than one hundred but for N on the order of a thousand, the time to run <codeph>Prepare()</codeph> can |
434 N is the number of columns in the table. The constant of proportionality |
420 start to become noticeable. </p> <p>When the bytecode is running and it needs |
435 is small in these cases so you should not have any problems for N |
421 to access the i-th column of a table, the values of the previous i-1 columns |
436 of less than one hundred but for N on the order of a thousand, the |
422 must be accessed first. So if you have a large number of columns, accessing |
437 time to run <codeph>Prepare()</codeph> can start to become noticeable. </p> <p>When the bytecode is running and it needs to access the i-th |
423 the last column can be an expensive operation. This fact also argues for putting |
438 column of a table, the values of the previous i-1 columns must be |
424 smaller and more frequently accessed columns early in the table. </p> <p>There |
439 accessed first. So if you have a large number of columns, accessing |
425 are certain optimizations that will only work if the table has 30 or fewer |
440 the last column can be an expensive operation. This fact also argues |
426 columns. The optimization that extracts all necessary information from an |
441 for putting smaller and more frequently accessed columns early in |
427 index and never refers to the underlying table works this way. So in some |
442 the table. </p> <p>There are certain optimizations that will only |
428 cases, keeping the number of columns in a table at or below 30 can result |
443 work if the table has 30 or fewer columns. The optimization that extracts |
429 in a 2-fold speed improvement. </p> <p>Indexes will only be used if they contain |
444 all necessary information from an index and never refers to the underlying |
430 30 or fewer columns. You can put as many columns in an index as you want, |
445 table works this way. So in some cases, keeping the number of columns |
431 but if the number is greater than 30, the index will never improve performance |
446 in a table at or below 30 can result in a 2-fold speed improvement. </p> <p>Indexes will only be used if they contain 30 or fewer columns. |
432 and will never do anything but take up space in your database file. </p> </section> |
447 You can put as many columns in an index as you want, but if the number |
|
448 is greater than 30, the index will never improve performance and will |
|
449 never do anything but take up space in your database file. </p> </section> |
433 </conbody><related-links> |
450 </conbody><related-links> |
434 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
451 <link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL |
435 </link> |
452 Overview</linktext></link> |
436 <link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
453 <link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL |
437 Guide</linktext></link> |
454 Server Guide</linktext></link> |
438 <link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
455 <link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
439 </link> |
456 </link> |
440 <link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
457 <link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL |
441 </link> |
458 DB Overview</linktext></link> |
442 <link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
459 <link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid |
443 Tables</linktext></link> |
460 Transient Tables</linktext></link> |
444 <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
461 <link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent |
445 Corruption</linktext></link> |
462 Datafile Corruption</linktext></link> |
446 <link><linktext/></link> |
463 <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"> |
447 <link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
464 <linktext>SQL Insertion Tips</linktext></link> |
448 Tips</linktext></link> |
465 <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL |
449 <link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
466 Schema Tips</linktext></link> |
450 Tips</linktext></link> |
467 <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL |
451 <link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext> |
468 Expressions</linktext></link> |
452 </link> |
469 <link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL |
453 <link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
470 Statement Tips</linktext></link> |
454 Tips</linktext></link> |
471 <link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL |
455 <link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
472 Joins</linktext></link> |
456 </link> |
|
457 <link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
473 <link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
458 Command</linktext></link> |
474 Command</linktext></link> |
459 <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE |
475 <link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL |
460 CLause Tips</linktext></link> |
476 WHERE CLause Tips</linktext></link> |
461 </related-links></concept> |
477 </related-links></concept> |