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