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