12 <concept id="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A" xml:lang="en"><title>SQL Index

    12 <concept id="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A" xml:lang="en"><title>SQL Index Tips</title><shortdesc>This document includes several tips for using SQL indexes.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

    13 Tips</title><shortdesc>This document includes several tips for using SQL indexes.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody>

    13 <section id="GUID-3895F9D0-DE9C-4375-B541-AC99CABB7B8A"><title>Introduction</title> <p>You can use indexes to speed up access. You create indexes automatically

    14 <section id="GUID-3895F9D0-DE9C-4375-B541-AC99CABB7B8A"><title>Introduction</title> <p>You can use indexes to speed up access.

    14 using PRIMARY KEY and UNIQUE. </p> <p><b>Intended audience:</b> </p> <p>This document is intended to be

    15 You create indexes automatically using PRIMARY KEY and UNIQUE. </p> <p><b>Intended

    15 used by Symbian platform licensees and third party application developers. </p> </section>

    16 audience:</b> </p> <p>This document is intended to be used by Symbian platform

    16 <section id="GUID-765F0DF1-ACB0-57DB-B9A8-3697E4637065"><title>Use

    17 licensees and third party application developers. </p> </section>

    17 an Index to Speed up Access</title> <p>Suppose you have a table like

    18 <section id="GUID-765F0DF1-ACB0-57DB-B9A8-3697E4637065"><title>Use an Index

    18 this: </p> <codeblock id="GUID-F70B25AB-A151-52CE-A413-1C62A2464D6A" xml:space="preserve">

    19 to Speed up Access</title> <p>Suppose you have a table like this: </p> <codeblock id="GUID-F70B25AB-A151-52CE-A413-1C62A2464D6A" xml:space="preserve">

    24 </codeblock> <p>Further suppose that this table contains thousands or millions

    23 </codeblock> <p>Further suppose that this table contains thousands

    25 of rows and you want to access a single row with a particular ID: </p> <codeblock id="GUID-B02FA452-4093-5383-BAFA-AE035919D720" xml:space="preserve">

    24 or millions of rows and you want to access a single row with a particular

    25 ID: </p> <codeblock id="GUID-B02FA452-4093-5383-BAFA-AE035919D720" xml:space="preserve">

    27 </codeblock> <p>The only want that SQLite can perform this query, and be certain

    27 </codeblock> <p>The only want that SQLite can perform this query,

    28 to get every row with the chosen ID, is to examine every single row, check

    28 and be certain to get every row with the chosen ID, is to examine

    29 the ID of that row, and return the content if the ID matches. Examining every

    29 every single row, check the ID of that row, and return the content

    30 single row this way is called a <i>full table scan</i>. </p> <p>Reading and

    30 if the ID matches. Examining every single row this way is called a <i>full table scan</i>. </p> <p>Reading and checking every row of a

    31 checking every row of a large table can be very slow, so you want to avoid

    31 large table can be very slow, so you want to avoid full table scans.

    32 full table scans. The usual way to do this is to create an index on the column

    32 The usual way to do this is to create an index on the column you are

    33 you are searching against. In the example above, an appropriate index would

    33 searching against. In the example above, an appropriate index would

    36 </codeblock> <p>With an index on the ID column, SQLite is able to use a binary

    36 </codeblock> <p>With an index on the ID column, SQLite is able to

    37 search to locate entries that contain a particular value of ID. So if the

    37 use a binary search to locate entries that contain a particular value

    38 table contains a million rows, the query can be satisfied with about 20 accesses

    38 of ID. So if the table contains a million rows, the query can be satisfied

    39 rather than 1000000 accesses. This is a huge performance improvement. </p> <p>One

    39 with about 20 accesses rather than 1000000 accesses. This is a huge

    40 of the features of the SQL language is that you do not have to figure out

    40 performance improvement. </p> <p>One of the features of the SQL language

    41 what indexes you may need in advance of coding your application. It is perfectly

    41 is that you do not have to figure out what indexes you may need in

    42 acceptable, even preferable, to write the code for your application using

    42 advance of coding your application. It is perfectly acceptable, even

    43 a database without any indexes. Then once the application is running and you

    43 preferable, to write the code for your application using a database

    44 can make speed measurements, add whatever indexes are needed in order to make

    44 without any indexes. Then once the application is running and you

    45 it run faster. </p> <p>When you add indexes, the query optimizer within the

    45 can make speed measurements, add whatever indexes are needed in order

    46 SQL compiler is able to find new more efficient bytecode procedures for carrying

    46 to make it run faster. </p> <p>When you add indexes, the query optimizer

    47 out the operations that your SQL statements specify. In other words, by adding

    47 within the SQL compiler is able to find new more efficient bytecode

    48 indexes late in the development cycle you have the power to completely reorganize

    48 procedures for carrying out the operations that your SQL statements

    49 your data access patterns without changing a single line of code. </p> </section>

    49 specify. In other words, by adding indexes late in the development

    50 <section id="GUID-BB1F17C5-1174-5DF4-AA61-611173237F3F"><title>Create Indexes

    50 cycle you have the power to completely reorganize your data access

    51 Automatically Using PRIMARY KEY and UNIQUE</title> <p>Any column of a table

    51 patterns without changing a single line of code. </p> </section>

    52 that is declared to be the PRIMARY KEY or that is declared UNIQUE will be

    52 <section id="GUID-BB1F17C5-1174-5DF4-AA61-611173237F3F"><title>Create

    53 indexed automatically. There is no need to create a separate index on that

    53 Indexes Automatically Using PRIMARY KEY and UNIQUE</title> <p>Any

    54 column using the CREATE INDEX statement. So, for example, this table declaration: </p> <codeblock id="GUID-E4BE6077-F639-5CE7-964A-276B0D58A129" xml:space="preserve">

    54 column of a table that is declared to be the PRIMARY KEY or that is

    55 declared UNIQUE will be indexed automatically. There is no need to

    56 create a separate index on that column using the CREATE INDEX statement.

    57 So, for example, this table declaration: </p> <codeblock id="GUID-E4BE6077-F639-5CE7-964A-276B0D58A129" xml:space="preserve">

    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">

    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">

    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

   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">

   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">

   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">

   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">

   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">

   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">

   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">

   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">

   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 “&lt;=”, or greater than or equal to “&gt;=”. </p> <p>The column that

   183 than or equal to “&lt;=”, or greater than or equal to “&gt;=”. </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">

   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">

   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">

   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">

   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">

   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">

   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">

   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">

   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>

   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">

   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">

   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">

   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">

   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">

   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">

   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">

   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">

   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>

   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>

   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>

   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>