--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/Symbian3/SDK/Source/GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita Wed Mar 31 11:11:55 2010 +0100
@@ -0,0 +1,461 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. -->
+<!-- This component and the accompanying materials are made available under the terms of the License
+"Eclipse Public License v1.0" which accompanies this distribution,
+and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". -->
+<!-- Initial Contributors:
+ Nokia Corporation - initial contribution.
+Contributors:
+-->
+<!DOCTYPE concept
+ PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd">
+<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>
+<section id="GUID-3895F9D0-DE9C-4375-B541-AC99CABB7B8A"><title>Introduction</title> <p>You can use indexes to speed up access.
+You create indexes automatically using PRIMARY KEY and UNIQUE. </p> <p><b>Intended
+audience:</b> </p> <p>This document is intended to be used by Symbian platform
+licensees and third party application developers. </p> </section>
+<section id="GUID-765F0DF1-ACB0-57DB-B9A8-3697E4637065"><title>Use an Index
+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">
+CREATE TABLE demo5(
+ id INTEGER,
+ content BLOB
+);
+</codeblock> <p>Further suppose that this table contains thousands or millions
+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">
+SELECT content FROM demo5 WHERE id=?
+</codeblock> <p>The only want that SQLite can perform this query, and be certain
+to get every row with the chosen ID, is to examine every single row, check
+the ID of that row, and return the content 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 large table can be very slow, so you want to avoid
+full table scans. The usual way to do this is to create an index on the column
+you are searching against. In the example above, an appropriate index would
+be this: </p> <codeblock id="GUID-82E337F1-2CA2-51B0-A7BC-071A83779A18" xml:space="preserve">
+CREATE INDEX demo5_idx1 ON demo5(id);
+</codeblock> <p>With an index on the ID column, SQLite is able to use a binary
+search to locate entries that contain a particular value of ID. So if the
+table contains a million rows, the query can be satisfied with about 20 accesses
+rather than 1000000 accesses. This is a huge performance improvement. </p> <p>One
+of the features of the SQL language is that you do not have to figure out
+what indexes you may need in advance of coding your application. It is perfectly
+acceptable, even preferable, to write the code for your application using
+a database without any indexes. Then once the application is running and you
+can make speed measurements, add whatever indexes are needed in order to make
+it run faster. </p> <p>When you add indexes, the query optimizer within the
+SQL compiler is able to find new more efficient bytecode procedures for carrying
+out the operations that your SQL statements specify. In other words, by adding
+indexes late in the development cycle you have the power to completely reorganize
+your data access patterns without changing a single line of code. </p> </section>
+<section id="GUID-BB1F17C5-1174-5DF4-AA61-611173237F3F"><title>Create Indexes
+Automatically Using PRIMARY KEY and UNIQUE</title> <p>Any column of a table
+that is declared to be the PRIMARY KEY or that is declared UNIQUE will be
+indexed automatically. There is no need to create a separate index on that
+column using the CREATE INDEX statement. So, for example, this table declaration: </p> <codeblock id="GUID-E4BE6077-F639-5CE7-964A-276B0D58A129" xml:space="preserve">
+CREATE TABLE demo39a(
+ id INTEGER,
+ content BLOB
+);
+
+CREATE INDEX demo39_idx1 ON demo39a(id);
+</codeblock> <p>Is roughly equivalent to the following: </p> <codeblock id="GUID-DB3167E0-FA95-50CA-92C7-102B5C2C13E3" xml:space="preserve">
+CREATE TABLE demo39b(
+ id INTEGER UNIQUE,
+ content BLOB
+);
+</codeblock> <p>The two examples above are “roughly” equivalent, but not exactly
+equivalent. Both tables have an index on the ID column. In the first case,
+the index is created explicitly. In the second case, the index is implied
+by the UNIQUE keyword in the type declaration of the ID column. Both table
+designs use exactly the same amount of disk space, and both will run queries
+such as </p> <codeblock id="GUID-7ACAE270-6D20-557B-B7D1-C90EDD757E43" xml:space="preserve">
+SELECT content FROM demo39 WHERE id=?
+</codeblock> <p>using exactly the same bytecode. The only difference is that
+table demo39a lets you insert multiple rows with the same ID whereas table
+demo39b will raise an exception if you try to insert a new row with the same
+ID as an existing row. </p> <p>If you use the UNIQUE keyword in the CREATE
+INDEX statement of demo39a, like this: </p> <codeblock id="GUID-0EE5E186-CC4A-5CC3-AEAE-F1482F1F8F9A" xml:space="preserve">
+CREATE UNIQUE INDEX demo39_idx1 ON demo39a(id);
+</codeblock> <p>Then both table designs really would be exactly the same in
+every way. In fact, whenever SQLite sees the UNIQUE keyword on a column type
+declaration, all it does is create an automatic unique index on that column. </p> <p>The
+PRIMARY KEY modifier on a column type declaration works like UNIQUE; it causes
+a unique index to be created automatically. The main difference is that you
+are only allowed to have a single PRIMARY KEY. This restriction of only allowing
+a 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. Some SQL database
+engines actually implement PRIMARY KEYs this way. But with SQLite, a PRIMARY
+KEY is like any other UNIQUE column, with only one exception: INTEGER PRIMARY
+KEY is a special case which is handled differently, as described in the next
+section. </p> </section>
+<section id="GUID-BF7A0301-8490-58ED-BB37-FAC403A84230"><title>Use Multi-Column
+Indexes</title> <p>SQLite is able to make use of multi-column 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 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
+an example, suppose you have a table and index declared as follows: </p> <codeblock id="GUID-C18C97F7-23CA-5636-9F00-130A8FB3DEF5" xml:space="preserve">
+CREATE TABLE demo314(a,b,c,d,e,f,g);
+CREATE INDEX demo314_idx ON demo314(a,b,c,d,e,f);
+</codeblock> <p>Then the index might be used to help with a query that contained
+a WHERE clause like this: </p> <codeblock id="GUID-8A0944F4-1ACF-5267-B49F-EB83EFBB5670" xml:space="preserve">
+... WHERE a=1 AND b='Smith' AND c=1
+</codeblock> <p>All three terms of the WHERE clause would be used together
+with the index in order to narrow the search. But the index could not be used
+if there WHERE clause said: </p> <codeblock id="GUID-B5F1C17F-0F5E-5FC2-A9A4-DF19D699A076" xml:space="preserve">
+... WHERE b='Smith' AND c=1
+</codeblock> <p>The second WHERE clause does not contain equality terms for
+a prefix of the columns in the index because it omits a term for the “a” column. </p> <p>In
+a case like this: </p> <codeblock id="GUID-EF2CFE7D-0456-5414-847D-BADCC057CFD8" xml:space="preserve">
+... WHERE a=1 AND c=1
+</codeblock> <p>Only the “a=1” term in the WHERE clause could be used to help
+narrow the search. The “c=1” term is not part of the prefix of terms in the
+index which have equality constraints because there is no equality constraint
+on the “b” column. </p> <p>SQLite only allows a single index to be used per
+table within a simple SQL statement. For UPDATE and DELETE statements, this
+means that only a single index can ever be used, since those statements can
+only operate on a single table at a time. </p> <p>In a simple SELECT statement
+multiple indexes can be used if the SELECT statement is a join – one index
+per table in the join. In a compound SELECT statement (two or more SELECT
+statements connected by UNION or INTERSECT or EXCEPT) each SELECT statement
+is treated separately and can have its own indexes. Likewise, SELECT statements
+that appear in subexpressions are treated separately. </p> <p>Some other SQL
+database engines (for example PostgreSQL) allow multiple indexes to be used
+for each table in a SELECT. For example, if you had a table and index in PostgreSQL
+like this: </p> <codeblock id="GUID-F5DE8F24-7471-5992-9896-295CE173D855" xml:space="preserve">
+CREATE TABLE pg1(a INT, b INT, c INT, d INT);
+CREATE INDEX pg1_ix1 ON pg1(a);
+CREATE INDEX pg1_ix2 ON pg1(b);
+CREATE INDEX pg1_ix3 ON pg1(c);
+</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">
+SELECT d FROM pg1 WHERE a=5 AND b=11 AND c=99;
+</codeblock> <p>Then PostgreSQL might attempt to optimize the query 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 a single index to use in
+the query. It might select any of the three indexes shown, depending on which
+one the optimizer things will give the best speedup. But in every case it
+will only select a single index and only a single term of the WHERE clause
+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">
+CREATE INDEX pg1_ix_all ON pg1(a,b,c);
+</codeblock> <p>If the pg1_ix_all index is available for use when the SELECT
+statement above is prepared, SQLite will likely choose it over any of the
+single-column indexes because the multi-column 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 table by rewriting the query. Instead of the SELECT statement
+shown above, if you rewrite it as this: </p> <codeblock id="GUID-D7DE75D4-BB01-50DF-A9DC-956A83DED5D0" xml:space="preserve">
+SELECT d FROM pg1 WHERE RowID IN (
+ SELECT RowID FROM pg1 WHERE a=5
+ INTERSECT
+ SELECT RowID FROM pg1 WHERE b=11
+ INTERSECT
+ SELECT RowID FROM pg1 WHERE c=99
+)
+</codeblock> <p>Then each of the individual SELECT statements will using a
+different single-column index and their results will be combined by the outer
+SELECT statement to give the correct result. The other SQL database engines
+like PostgreSQL that are able to make use of multiple indexes per table do
+so by treating the simpler SELECT statement shown first as if they where the
+more complicated SELECT statement shown here. </p> </section>
+<section id="GUID-E90057A8-70B6-590C-B8AE-616DA25BB543"><title>Use Inequality
+Constraints on the Last Index Term</title> <p>Terms in the WHERE clause of
+a query or UPDATE or DELETE statement are mostly likely to trigger the use
+of an index if they are an equality constraint – in other words if the term
+consists of the name of an indexed column, an equal sign (“=”), and an expression. </p> <p>So,
+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">
+CREATE TABLE demo315(a,b,c,d);
+CREATE INDEX demo315_idx1 ON demo315(a,b,c);
+</codeblock> <p>And a query like this: </p> <codeblock id="GUID-A2B7DA9F-DB82-5D06-80E2-7AF714E403D5" xml:space="preserve">
+SELECT d FROM demo315 WHERE a=512;
+</codeblock> <p>The single “a=512” term of the WHERE clause qualifies as an
+equality constraint and is likely to provoke the use of the demo315_idx1 index. </p> <p>SQLite
+supports two other kinds of equality constraints. One is the IN operator: </p> <codeblock id="GUID-EA5D7637-A6B8-5BC0-A72E-D576B0F945A3" xml:space="preserve">
+SELECT d FROM demo315 WHERE a IN (512,1024);
+SELECT d FROM demo315 WHERE a IN (SELECT x FROM someothertable);
+</codeblock> <p>There other is the IS NULL constraint: </p> <codeblock id="GUID-B2C1C84B-C33D-55C5-8484-24B28EFC8E37" xml:space="preserve">
+SELECT d FROM demo315 WHERE a IS NULL;
+</codeblock> <p>SQLite allows at most one term of an index to be constrained
+by an inequality such as less than “<”, greater than “>”, less than or
+equal to “<=”, or greater than or equal to “>=”. </p> <p>The column that
+the inequality constrains will be the right-most 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">
+SELECT d FROM demo315 WHERE a=5 AND b>11 AND c=1;
+</codeblock> <p>Only the first two terms of the WHERE clause will be used
+with the demo315_idx1 index. The third term, the “c=1” constraint, cannot
+be used because the “c” column occurs to the right of the “b” 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 as the two inequalities
+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">
+SELECT d FROM demo315 WHERE a=5 AND b>11 AND b<23;
+</codeblock> <p>All three terms of the WHERE clause will be used because the
+two inequalities on the “b” column provide an upper and lower bound on the
+value of “b”. </p> <p>SQLite will only use the four inequalities mentioned
+above to help constrain a search: “<”, “>”, “<=”, and “>=”. Other inequality
+operators such as not equal to (“!=” or “<>”) and NOT NULL are not helpful
+to the query optimizer and will never be used to control an index and help
+make the query run faster. </p> </section>
+<section id="GUID-CAD0C181-37E7-578A-A7E1-7843447C247F"><title>Use Indexes
+To Help ORDER BY Clauses Evaluate Faster</title> <p>The default method for
+evaluating an ORDER BY clause in a SELECT statement is to first evaluate the
+SELECT statement and store the results in a temporary tables, then sort the
+temporary table according to the ORDER BY clause and scan the sorted temporary
+table to generate the final output. </p> <p>This method always works, but
+it requires three passes over the data (one pass to generate the result set,
+a second pass to sort the result set, and a third pass to output the results)
+and it requires a temporary storage space sufficiently large to contain the
+entire results set. </p> <p>Where possible, SQLite will avoid storing and
+sorting the result set by using an index that causes the 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 an index that covers the
+same columns specified in the ORDER BY clause. For example, if the table and
+index are like this: </p> <codeblock id="GUID-F0103033-C5C8-5177-8AD7-70BCC45C33C9" xml:space="preserve">
+CREATE TABLE demo316(a,b,c,data);
+CREATE INDEX idx316 ON demo316(a,b,c);
+</codeblock> <p>And you do a query like this: </p> <codeblock id="GUID-D67BB6FF-E213-5B86-A2C1-E1992DA96A62" xml:space="preserve">
+SELECT data FROM demo316 ORDER BY a,b,c;
+</codeblock> <p>SQLite will use the idx316 index to implement the ORDER BY
+clause, obviating the need for temporary storage space and a separate sorting
+pass. </p> <p>An index can be used to satisfy the search constraints of a
+WHERE clause and to impose the ORDER BY ordering of outputs all at once. The
+trick is for the ORDER BY clause terms to occur immediately after the WHERE
+clause terms in the index. For example, one can write: </p> <codeblock id="GUID-02063968-34B5-5766-9D02-86D696D39C1E" xml:space="preserve">
+SELECT data FROM demo316 WHERE a=5 ORDER BY b,c;
+</codeblock> <p>The “a” column is used in the WHERE clause and the immediately
+following terms of the index, “b” and “c” are used in the ORDER BY clause.
+So in this case the idx316 index would be used 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 ORDER BY clause term “c” immediate follows
+the WHERE clause terms “a” and “b” in the index: </p> <codeblock id="GUID-6760EC7E-E86A-5EBD-BDDD-32A68BE78A9E" xml:space="preserve">
+SELECT data FROM demo316 WHERE a=5 AND b=17 ORDER BY c;
+</codeblock> <p>But now consider this: </p> <codeblock id="GUID-9363996C-8C30-5E04-B05F-392C8262F1F6" xml:space="preserve">
+SELECT data FROM demo316 WHERE a=5 ORDER BY c;
+</codeblock> <p>Here there is a gap between the ORDER BY term “c” and the
+WHERE clause term “a”. So the idx316 index cannot be used to satisfy both
+the WHERE clause and the ORDER BY clause. The index will be used on the WHERE
+clause and a separate sorting pass will occur to put the results in the correct
+order. </p> </section>
+<section id="GUID-109AF0DA-A054-504A-A432-76BD145B2AC4"><title>Add Result
+Columns To The End Of Indexes</title> <p>Queries will sometimes run faster
+if their result columns appear in the right-most entries of an index. Consider
+the following example: </p> <codeblock id="GUID-63292052-B523-5671-B3EE-E10A66C7275F" xml:space="preserve">
+CREATE TABLE demo317(a,b,c,data);
+CREATE INDEX idx317 ON demo316(a,b,c);
+</codeblock> <p>A query where all result column terms appears in the index,
+such as </p> <codeblock id="GUID-41F740E7-EAFC-583B-BFE6-E63DBEA354D7" xml:space="preserve">
+SELECT c FROM demo317 WHERE a=5 ORDER BY b;
+</codeblock> <p>will typically run about twice as fast or faster than 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">
+SELECT data FROM demo317 WHERE a=5 ORDER BY b;
+</codeblock> <p>The reason for this is that when all information is contained
+within the index entry only a single search has to be made for each row of
+output. But when some of the information is in the index and other parts are
+in the table, first there must be a search for the appropriate index entry
+then a separate search is made for the appropriate table row based on the
+RowID found in the index entry. 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 additional columns to an index makes the database
+file larger. So when developing an application, the programmer will need to
+make a space versus time trade-off to determine whether the extra columns
+should be added to the index or not. </p> <p>Note that if any column of the
+result must be obtained from the original table, then the table row will have
+to be searched for anyhow. There will be no speed advantage, so you might
+as well omit the extra columns from the end of the index and save on storage
+space. The speed-up described in this section can only be realized when every
+column in a table is obtainable from the index. </p> <p>Taking into account
+the results of the previous few sections, the best set of columns to put in
+an index can be described as follows: </p> <ul>
+<li id="GUID-EBF4DEFB-2F5F-5D78-92FA-06FEAB0C3650"><p>The first columns in
+the index should be columns that have equality constraints in the WHERE clause
+of the query. </p> </li>
+<li id="GUID-E5CB725C-6304-5946-9E18-E69B5F1A6A88"><p>The second group of
+columns should match the columns specified in the ORDER BY clause. </p> </li>
+<li id="GUID-FBC00251-C3AD-5AC0-9102-EF66EA37DE4E"><p>Add additional columns
+to the end of the index that are used in the result set of the query. </p> </li>
+</ul> </section>
+<section id="GUID-D7B5B389-E031-5512-8186-235B22F0D9C1"><title>Resolve Indexing
+Ambiguities Using the Unary “+” Operator</title> <p>The SQLite query optimizer
+usually does a good job of choosing the best index to use for a particular
+query, especially if ANALYZE has been run to provide it with index performance
+statistics. But occasions do arise where it is useful to give the optimizer
+hints. </p> <p>One of the easiest ways to control the operation of the optimizer
+is to disqualify terms in the WHERE clause or ORDER BY clause as candidates
+for optimization by using the unary “+” operator. </p> <p>In SQLite, a unary
+“+” operator is a no-op. It makes no change to its operand, even if the operand
+is something other than a number. So you can always prefix a “+” to an expression
+in without changing the meaning of the expression. As the optimizer will only
+use terms in WHERE, HAVING, or ON clauses that have an index column name on
+one side of a comparison operator, you can prevent such a term from being
+used by the optimizer by prefixing the column name with a “+”. </p> <p>For
+example, suppose you have a database with a schema like this: </p> <codeblock id="GUID-E7747EFD-FE58-5EA4-88B3-097C0A303F52" xml:space="preserve">
+CREATE TABLE demo321(a,b,c,data);
+CREATE INDEX idx321a ON demo321(a);
+CREATE INDEX idx321b ON demo321(b);
+</codeblock> <p>If you issue a query such as this: </p> <codeblock id="GUID-87BD59FC-33A8-598B-B91F-607B26F7349D" xml:space="preserve">
+SELECT data FROM demo321 WHERE a=5 AND b=11;
+</codeblock> <p>The query optimizer might use the “a=5” term with idx321a
+or it might use the “b=11” term with the idx321b index. But if you want to
+force the use of the idx321a index you can accomplish that by disqualifying
+the second term of the WHERE clause as a candidate for optimization using
+a unary “+” like this: </p> <codeblock id="GUID-E6EAB459-726A-5FE4-8065-6C46AC2C5B5C" xml:space="preserve">
+SELECT data FROM demo321 WHERE a=5 AND +b=11;
+</codeblock> <p>The “+” in front of the “b=11” turns the left-hand side of
+the equals comparison operator into an expression instead of an indexed column
+name. The optimizer will then not recognize that the second term can be used
+with an index and so the optimizer is compelled to use the first “a=5” term. </p> <p>The
+unary “+” operator can also be used to disable ORDER BY clause optimizations.
+Consider this query: </p> <codeblock id="GUID-0488D466-77B7-50E0-AB85-FF033A2D75DC" xml:space="preserve">
+SELECT data FROM demo321 WHERE a=5 ORDER BY b;
+</codeblock> <p>The optimizer has the choice of using the “a=5” term of the
+WHERE clause with idx321a to restrict the search. Or it might choose to use
+do a full table scan with idx321b to satisfy the ORDER BY clause and thus
+avoid a separate sorting pass. You can force one choice or the other using
+a unary “+”. </p> <p>To force the use of idx321a on the WHERE clause, add
+the unary “+” in from of the “b” in the ORDER BY clause: </p> <codeblock id="GUID-E55A085F-D91F-58E0-B964-317BB3A9D7ED" xml:space="preserve">
+SELECT data FROM demo321 WHERE a=5 ORDER BY +b;
+</codeblock> <p>To go the other way and force the idx321b index to be used
+to satisfy the ORDER BY clause, disqualify the WHERE term by prefixing with
+a unary “+”: </p> <codeblock id="GUID-D97EF52A-1F74-57EB-AC11-7911B4E088B3" xml:space="preserve">
+SELECT data FROM demo321 WHERE +a=5 ORDER BY b;
+</codeblock> <p>The reader is cautioned not to overuse the unary “+” operator.
+The SQLite query optimizer usually picks the best index without any outside
+help. Premature use of unary “+” can confuse the optimizer and cause less
+than optimal performance. But in some cases it is useful to be able override
+the decisions of the optimizer, and the unary “+” operator is an excellent
+way to do this when it becomes necessary. </p> </section>
+<section id="GUID-7BEBC49C-0528-5D58-9626-2A92F3D0D9E8"><title>Avoid Indexing
+Large BLOBs and CLOBs</title> <p>SQLite stores indexes as b-trees. Each b-tree
+node uses one page of the database file. In order to maintain an acceptable
+fan-out, the b-tree module within SQLite requires that at least 4 entries
+must fit on each page of a b-tree. There is also some overhead associated
+with each b-tree page. So at the most there is about 250 bytes of space available
+on the main b-tree page for each index entry. </p> <p>If an index entry exceeds
+this allotment of approximately 250 bytes excess bytes are spilled to overflow
+pages. There is no arbitrary limit on the number of overflow pages or on the
+length of a b-tree entry, but for maximum efficiency it is best to avoid overflow
+pages, especially in indexes. This means that you should strive to keep the
+number of bytes in each index entry below 250. </p> <p>If you keep the size
+of indexes significantly smaller than 250 bytes, then the b-tree fan-out is
+increased and the binary search algorithm used to search for entries in an
+index has fewer pages to examine and therefore runs faster. So the fewer bytes
+used in each index entry the better, at least from a performance perspective. </p> <p>For
+these reasons, it is recommended that you avoid indexing large BLOBs and CLOBs.
+SQLite will continue to work when large BLOBs 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 BLOB or CLOB as the key, then by all means use an index.
+An index on a large BLOB or CLOB is not as fast as an index using more compact
+data types such as integers, but it is still many order of magnitude faster
+than doing a full table scan. So to be more precise, the advice of this section
+is that you should design your applications so that you do not need to lookup
+entries using a large BLOB or CLOB as the key. Try to arrange to have compact
+keys consisting of short strings or integers. </p> <p>Note that many other
+SQL database engines disallow the indexing of BLOBs and CLOBs in the first
+place. You simple cannot do it. SQLite is more flexible that most in that
+it does allow BLOBs and CLOBs to be indexed and it will use those indexes
+when appropriate. But for maximum performance, it is best to use smaller search
+keys. </p> </section>
+<section id="GUID-DD40F29F-DF93-536E-9B52-F9B9FF45155D"><title>Avoid Excess
+Indexes</title> <p>Some developers approach SQL-based application development
+with the attitude that indexes never hurt and that the more indexes you have,
+the faster your application will run. This is definitely not the case. There
+is a costs associated with each new index you create: </p> <ul>
+<li id="GUID-FD257BF7-F938-54B5-AC03-9536712D6281"><p>Each new index takes
+up additional space in the database file. The more indexes you have, the larger
+your database files will become for the same amount of data. </p> </li>
+<li id="GUID-E1B74FB6-246A-5148-AF06-04E1B4B949F1"><p>Every INSERT and UPDATE
+statement modifies both the original table and all indexes on that table.
+So the performance of INSERT and UPDATE decreases linearly with the number
+of indexes. </p> </li>
+<li id="GUID-56AAE2D1-71D6-5A23-8190-B0C80B204DED"><p>Compiling new SQL statements
+using <codeph>Prepare()</codeph> takes longer when there are more indexes
+for the optimizer to choose between. </p> </li>
+<li id="GUID-24B7F7D8-FAA9-5C78-B3C7-B886FA774C0B"><p>Surplus indexes give
+the optimizer more opportunities to make a bad choice. </p> </li>
+</ul> <p>Your policy on indexes should be to avoid them wherever you can.
+Indexes are powerful medicine and can work wonders to improve the performance
+of a program. But just as too many drugs can be worse than none at all, so
+also can too many indexes cause more harm than good. </p> <p>When building
+a new application, a good approach is to omit all explicitly declared indexes
+in the beginning and only add indexes as needed to address specific performance
+problems. </p> <p>Take care to avoid redundant indexes. For example, consider
+this schema: </p> <codeblock id="GUID-89F20101-1628-5783-82B0-2ABE84078C7D" xml:space="preserve">
+CREATE TABLE demo323a(a,b,c);
+CREATE INDEX idx323a1 ON demo323(a);
+CREATE INDEX idx323a2 ON demo323(a,b);
+</codeblock> <p>The idx323a1 index is redundant and can be eliminated. Anything
+that the idx323a1 index can do the idx323a2 index can do better. </p> <p>Other
+redundancies are not quite as apparent as the above. Recall that any column
+or columns that are declared UNIQUE or PRIMARY KEY (except for the special
+case of INTEGER PRIMARY KEY) are automatically indexed. So in the following
+schema: </p> <codeblock id="GUID-2FE7B726-4027-518C-9217-B4BD1ECDA991" xml:space="preserve">
+CREATE TABLE demo323b(x TEXT PRIMARY KEY, y INTEGER UNIQUE);
+CREATE INDEX idx323b1 ON demo323b(x);
+CREATE INDEX idx323b2 ON demo323b(y);
+</codeblock> <p>Both indexes are redundant and can be eliminated with no loss
+in query performance. Occasionally one sees a novice SQL programmer use both
+UNIQUE and PRIMARY KEY on the same column: </p> <codeblock id="GUID-CDE12649-BDB4-58D4-8981-02628BDF5C79" xml:space="preserve">
+CREATE TABLE demo323c(p TEXT UNIQUE PRIMARY KEY, q);
+</codeblock> <p>This has the effect of creating two indexes on the “p” column
+– one for the UNIQUE keywords and another for the PRIMARY KEY keyword. Both
+indexes are identical so clearly one can be omitted. A PRIMARY KEY is guaranteed
+to always be unique so the UNIQUE keyword can be removed from the demo323c
+table definition with no ambiguity or loss of functionality. </p> <p>It is
+not a fatal error to create too many indexes or redundant indexes. SQLite
+will continue to generate the correct answers but it may take longer to produce
+those answers and the resulting database files might be a little larger. So
+for best results, keep the number of indexes to a minimum. </p> </section>
+<section id="GUID-9337E315-BB5A-56D0-8319-6C398D26151F"><title>Avoid Tables
+and Indexes with an Excessive Number of Columns</title> <p>SQLite places no
+arbitrary limits on the number of columns in a table or index. There are known
+commercial applications using SQLite that construct tables with tens of thousands
+of columns each. And these applications actually work. </p> <p>However the
+database engine is optimized for the common case of tables with no more than
+a few dozen columns. For best performance you should try to stay in the optimized
+region. Furthermore, we note that relational databases with a large number
+of columns are usually not well normalized. So even apart from performance
+considerations, if you find your design has tables with more than a dozen
+or so columns, you really need to rethink how you are building your application. </p> <p>There
+are a number of places in <codeph>Prepare()</codeph> that run in time O(N<sup>2</sup>)
+where N is the number of columns in the table. The constant of proportionality
+is small in these cases so you should not have any problems for N of less
+than one hundred but for N on the order of a thousand, the 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 column of a table, the values of the previous i-1 columns
+must be accessed first. So if you have a large number of columns, accessing
+the last column can be an expensive operation. This fact also argues for putting
+smaller and more frequently accessed columns early in the table. </p> <p>There
+are certain optimizations that will only work if the table has 30 or fewer
+columns. The optimization that extracts all necessary information from an
+index and never refers to the underlying table works this way. So in some
+cases, keeping the number of columns 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. You can put as many columns in an index as you want,
+but if the number is greater than 30, the index will never improve performance
+and will never do anything but take up space in your database file. </p> </section>
+</conbody><related-links>
+<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext>
+</link>
+<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server
+Guide</linktext></link>
+<link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext>
+</link>
+<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext>
+</link>
+<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient
+Tables</linktext></link>
+<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile
+Corruption</linktext></link>
+<link><linktext/></link>
+<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion
+Tips</linktext></link>
+<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema
+Tips</linktext></link>
+<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext>
+</link>
+<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement
+Tips</linktext></link>
+<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext>
+</link>
+<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE
+Command</linktext></link>
+<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL WHERE
+CLause Tips</linktext></link>
+</related-links></concept>
\ No newline at end of file