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