author | Graeme Price <GRAEME.PRICE@NOKIA.COM> |
Fri, 15 Oct 2010 14:32:18 +0100 | |
changeset 15 | 307f4279f433 |
parent 13 | 48780e181b38 |
permissions | -rw-r--r-- |
13
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
1 |
<?xml version="1.0" encoding="utf-8"?> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
2 |
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
3 |
<!-- This component and the accompanying materials are made available under the terms of the License |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
4 |
"Eclipse Public License v1.0" which accompanies this distribution, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
5 |
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
6 |
<!-- Initial Contributors: |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
7 |
Nokia Corporation - initial contribution. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
8 |
Contributors: |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
9 |
--> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
10 |
<!DOCTYPE concept |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
11 |
PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
12 |
<concept id="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A" xml:lang="en"><title>SQL Index Tips</title><shortdesc>This document includes several tips for using SQL indexes.</shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
13 |
<section id="GUID-3895F9D0-DE9C-4375-B541-AC99CABB7B8A"><title>Introduction</title> <p>You can use indexes to speed up access. You create indexes automatically |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
14 |
using PRIMARY KEY and UNIQUE. </p> <p><b>Intended audience:</b> </p> <p>This document is intended to be |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
15 |
used by Symbian platform licensees and third party application developers. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
16 |
<section id="GUID-765F0DF1-ACB0-57DB-B9A8-3697E4637065"><title>Use |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
17 |
an Index to Speed up Access</title> <p>Suppose you have a table like |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
18 |
this: </p> <codeblock id="GUID-F70B25AB-A151-52CE-A413-1C62A2464D6A" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
19 |
CREATE TABLE demo5( |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
20 |
id INTEGER, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
21 |
content BLOB |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
22 |
); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
23 |
</codeblock> <p>Further suppose that this table contains thousands |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
24 |
or millions of rows and you want to access a single row with a particular |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
25 |
ID: </p> <codeblock id="GUID-B02FA452-4093-5383-BAFA-AE035919D720" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
26 |
SELECT content FROM demo5 WHERE id=? |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
27 |
</codeblock> <p>The only want that SQLite can perform this query, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
28 |
and be certain to get every row with the chosen ID, is to examine |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
29 |
every single row, check the ID of that row, and return the content |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
30 |
if the ID matches. Examining every single row this way is called a <i>full table scan</i>. </p> <p>Reading and checking every row of a |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
31 |
large table can be very slow, so you want to avoid full table scans. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
32 |
The usual way to do this is to create an index on the column you are |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
33 |
searching against. In the example above, an appropriate index would |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
34 |
be this: </p> <codeblock id="GUID-82E337F1-2CA2-51B0-A7BC-071A83779A18" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
35 |
CREATE INDEX demo5_idx1 ON demo5(id); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
36 |
</codeblock> <p>With an index on the ID column, SQLite is able to |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
37 |
use a binary search to locate entries that contain a particular value |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
38 |
of ID. So if the table contains a million rows, the query can be satisfied |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
39 |
with about 20 accesses rather than 1000000 accesses. This is a huge |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
40 |
performance improvement. </p> <p>One of the features of the SQL language |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
41 |
is that you do not have to figure out what indexes you may need in |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
42 |
advance of coding your application. It is perfectly acceptable, even |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
43 |
preferable, to write the code for your application using a database |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
44 |
without any indexes. Then once the application is running and you |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
45 |
can make speed measurements, add whatever indexes are needed in order |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
46 |
to make it run faster. </p> <p>When you add indexes, the query optimizer |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
47 |
within the SQL compiler is able to find new more efficient bytecode |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
48 |
procedures for carrying out the operations that your SQL statements |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
49 |
specify. In other words, by adding indexes late in the development |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
50 |
cycle you have the power to completely reorganize your data access |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
51 |
patterns without changing a single line of code. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
52 |
<section id="GUID-BB1F17C5-1174-5DF4-AA61-611173237F3F"><title>Create |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
53 |
Indexes Automatically Using PRIMARY KEY and UNIQUE</title> <p>Any |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
54 |
column of a table that is declared to be the PRIMARY KEY or that is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
55 |
declared UNIQUE will be indexed automatically. There is no need to |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
56 |
create a separate index on that column using the CREATE INDEX statement. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
57 |
So, for example, this table declaration: </p> <codeblock id="GUID-E4BE6077-F639-5CE7-964A-276B0D58A129" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
58 |
CREATE TABLE demo39a( |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
59 |
id INTEGER, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
60 |
content BLOB |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
61 |
); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
62 |
|
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
63 |
CREATE INDEX demo39_idx1 ON demo39a(id); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
64 |
</codeblock> <p>Is roughly equivalent to the following: </p> <codeblock id="GUID-DB3167E0-FA95-50CA-92C7-102B5C2C13E3" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
65 |
CREATE TABLE demo39b( |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
66 |
id INTEGER UNIQUE, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
67 |
content BLOB |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
68 |
); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
69 |
</codeblock> <p>The two examples above are “roughly” equivalent, but |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
70 |
not exactly equivalent. Both tables have an index on the ID column. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
71 |
In the first case, the index is created explicitly. In the second |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
72 |
case, the index is implied by the UNIQUE keyword in the type declaration |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
73 |
of the ID column. Both table designs use exactly the same amount of |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
74 |
disk space, and both will run queries such as </p> <codeblock id="GUID-7ACAE270-6D20-557B-B7D1-C90EDD757E43" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
75 |
SELECT content FROM demo39 WHERE id=? |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
76 |
</codeblock> <p>using exactly the same bytecode. The only difference |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
77 |
is that table demo39a lets you insert multiple rows with the same |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
78 |
ID whereas table demo39b will raise an exception if you try to insert |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
79 |
a new row with the same ID as an existing row. </p> <p>If you use |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
80 |
the UNIQUE keyword in the CREATE INDEX statement of demo39a, like |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
81 |
this: </p> <codeblock id="GUID-0EE5E186-CC4A-5CC3-AEAE-F1482F1F8F9A" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
82 |
CREATE UNIQUE INDEX demo39_idx1 ON demo39a(id); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
83 |
</codeblock> <p>Then both table designs really would be exactly the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
84 |
same in every way. In fact, whenever SQLite sees the UNIQUE keyword |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
85 |
on a column type declaration, all it does is create an automatic unique |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
86 |
index on that column. </p> <p>The PRIMARY KEY modifier on a column |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
87 |
type declaration works like UNIQUE; it causes a unique index to be |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
88 |
created automatically. The main difference is that you are only allowed |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
89 |
to have a single PRIMARY KEY. This restriction of only allowing a |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
90 |
single PRIMARY KEY is part of the official SQL language definition. </p> <p>The idea is that a PRIMARY KEY is used to order the rows on disk. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
91 |
Some SQL database engines actually implement PRIMARY KEYs this way. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
92 |
But with SQLite, a PRIMARY KEY is like any other UNIQUE column, with |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
93 |
only one exception: INTEGER PRIMARY KEY is a special case which is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
94 |
handled differently, as described in the next section. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
95 |
<section id="GUID-BF7A0301-8490-58ED-BB37-FAC403A84230"><title>Use |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
96 |
Multi-Column Indexes</title> <p>SQLite is able to make use of multi-column |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
97 |
indexes. The rule is that if an index is over columns <i>X</i> <i> 0 </i>, <i>X</i> <i> 1 </i>, <i>X</i> <i> 2 </i>, ..., <i>X</i> <i> n </i> of some table, then the index can be used if the WHERE |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
98 |
clause contains equality constraints for some prefix of those columns <i>X</i> <i>0 </i>, <i>X</i> <i>1 </i>, <i>X</i> <i>2 </i>, ..., <i>X</i> <i>i </i> where <i>i</i> is less than <i>n</i>. </p> <p>As |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
99 |
an example, suppose you have a table and index declared as follows: </p> <codeblock id="GUID-C18C97F7-23CA-5636-9F00-130A8FB3DEF5" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
100 |
CREATE TABLE demo314(a,b,c,d,e,f,g); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
101 |
CREATE INDEX demo314_idx ON demo314(a,b,c,d,e,f); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
102 |
</codeblock> <p>Then the index might be used to help with a query |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
103 |
that contained a WHERE clause like this: </p> <codeblock id="GUID-8A0944F4-1ACF-5267-B49F-EB83EFBB5670" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
104 |
... WHERE a=1 AND b='Smith' AND c=1 |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
105 |
</codeblock> <p>All three terms of the WHERE clause would be used |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
106 |
together with the index in order to narrow the search. But the index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
107 |
could not be used if there WHERE clause said: </p> <codeblock id="GUID-B5F1C17F-0F5E-5FC2-A9A4-DF19D699A076" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
108 |
... WHERE b='Smith' AND c=1 |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
109 |
</codeblock> <p>The second WHERE clause does not contain equality |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
110 |
terms for a prefix of the columns in the index because it omits a |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
111 |
term for the “a” column. </p> <p>In a case like this: </p> <codeblock id="GUID-EF2CFE7D-0456-5414-847D-BADCC057CFD8" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
112 |
... WHERE a=1 AND c=1 |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
113 |
</codeblock> <p>Only the “a=1” term in the WHERE clause could be used |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
114 |
to help narrow the search. The “c=1” term is not part of the prefix |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
115 |
of terms in the index which have equality constraints because there |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
116 |
is no equality constraint on the “b” column. </p> <p>SQLite only allows |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
117 |
a single index to be used per table within a simple SQL statement. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
118 |
For UPDATE and DELETE statements, this means that only a single index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
119 |
can ever be used, since those statements can only operate on a single |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
120 |
table at a time. </p> <p>In a simple SELECT statement multiple indexes |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
121 |
can be used if the SELECT statement is a join – one index per table |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
122 |
in the join. In a compound SELECT statement (two or more SELECT statements |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
123 |
connected by UNION or INTERSECT or EXCEPT) each SELECT statement is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
124 |
treated separately and can have its own indexes. Likewise, SELECT |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
125 |
statements that appear in subexpressions are treated separately. </p> <p>Some other SQL database engines (for example PostgreSQL) allow |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
126 |
multiple indexes to be used for each table in a SELECT. For example, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
127 |
if you had a table and index in PostgreSQL like this: </p> <codeblock id="GUID-F5DE8F24-7471-5992-9896-295CE173D855" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
128 |
CREATE TABLE pg1(a INT, b INT, c INT, d INT); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
129 |
CREATE INDEX pg1_ix1 ON pg1(a); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
130 |
CREATE INDEX pg1_ix2 ON pg1(b); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
131 |
CREATE INDEX pg1_ix3 ON pg1(c); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
132 |
</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"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
133 |
SELECT d FROM pg1 WHERE a=5 AND b=11 AND c=99; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
134 |
</codeblock> <p>Then PostgreSQL might attempt to optimize the query |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
135 |
by using all three indexes, one for each term of the WHERE clause. </p> <p>SQLite does not work this way. SQLite is compelled to select |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
136 |
a single index to use in the query. It might select any of the three |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
137 |
indexes shown, depending on which one the optimizer things will give |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
138 |
the best speedup. But in every case it will only select a single index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
139 |
and only a single term of the WHERE clause will be used. </p> <p>SQLite |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
140 |
prefers to use a multi-column index such as this: </p> <codeblock id="GUID-40FB7075-1239-5089-BBC5-0D994F4A0C39" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
141 |
CREATE INDEX pg1_ix_all ON pg1(a,b,c); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
142 |
</codeblock> <p>If the pg1_ix_all index is available for use when |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
143 |
the SELECT statement above is prepared, SQLite will likely choose |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
144 |
it over any of the single-column indexes because the multi-column |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
145 |
index is able to make use of all 3 terms of the WHERE clause. </p> <p>You can trick SQLite into using multiple indexes on the same |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
146 |
table by rewriting the query. Instead of the SELECT statement shown |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
147 |
above, if you rewrite it as this: </p> <codeblock id="GUID-D7DE75D4-BB01-50DF-A9DC-956A83DED5D0" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
148 |
SELECT d FROM pg1 WHERE RowID IN ( |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
149 |
SELECT RowID FROM pg1 WHERE a=5 |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
150 |
INTERSECT |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
151 |
SELECT RowID FROM pg1 WHERE b=11 |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
152 |
INTERSECT |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
153 |
SELECT RowID FROM pg1 WHERE c=99 |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
154 |
) |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
155 |
</codeblock> <p>Then each of the individual SELECT statements will |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
156 |
using a different single-column index and their results will be combined |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
157 |
by the outer SELECT statement to give the correct result. The other |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
158 |
SQL database engines like PostgreSQL that are able to make use of |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
159 |
multiple indexes per table do so by treating the simpler SELECT statement |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
160 |
shown first as if they where the more complicated SELECT statement |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
161 |
shown here. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
162 |
<section id="GUID-E90057A8-70B6-590C-B8AE-616DA25BB543"><title>Use |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
163 |
Inequality Constraints on the Last Index Term</title> <p>Terms in |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
164 |
the WHERE clause of a query or UPDATE or DELETE statement are mostly |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
165 |
likely to trigger the use of an index if they are an equality constraint |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
166 |
– in other words if the term consists of the name of an indexed column, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
167 |
an equal sign (“=”), and an expression. </p> <p>So, for example, if |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
168 |
you have a table and index that look like this: </p> <codeblock id="GUID-84AADB9D-5853-57C2-B489-87DC7FB7AADE" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
169 |
CREATE TABLE demo315(a,b,c,d); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
170 |
CREATE INDEX demo315_idx1 ON demo315(a,b,c); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
171 |
</codeblock> <p>And a query like this: </p> <codeblock id="GUID-A2B7DA9F-DB82-5D06-80E2-7AF714E403D5" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
172 |
SELECT d FROM demo315 WHERE a=512; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
173 |
</codeblock> <p>The single “a=512” term of the WHERE clause qualifies |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
174 |
as an equality constraint and is likely to provoke the use of the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
175 |
demo315_idx1 index. </p> <p>SQLite supports two other kinds of equality |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
176 |
constraints. One is the IN operator: </p> <codeblock id="GUID-EA5D7637-A6B8-5BC0-A72E-D576B0F945A3" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
177 |
SELECT d FROM demo315 WHERE a IN (512,1024); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
178 |
SELECT d FROM demo315 WHERE a IN (SELECT x FROM someothertable); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
179 |
</codeblock> <p>There other is the IS NULL constraint: </p> <codeblock id="GUID-B2C1C84B-C33D-55C5-8484-24B28EFC8E37" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
180 |
SELECT d FROM demo315 WHERE a IS NULL; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
181 |
</codeblock> <p>SQLite allows at most one term of an index to be constrained |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
182 |
by an inequality such as less than “<”, greater than “>”, less |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
183 |
than or equal to “<=”, or greater than or equal to “>=”. </p> <p>The column that the inequality constrains will be the right-most |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
184 |
term of the index that is used. So, for example, in this query: </p> <codeblock id="GUID-563231B5-EC3A-57C2-BC6F-1A8129ADE308" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
185 |
SELECT d FROM demo315 WHERE a=5 AND b>11 AND c=1; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
186 |
</codeblock> <p>Only the first two terms of the WHERE clause will |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
187 |
be used with the demo315_idx1 index. The third term, the “c=1” constraint, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
188 |
cannot be used because the “c” column occurs to the right of the “b” |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
189 |
column in the index and the “b” column is constrained by an inequality. </p> <p>SQLite allows up to two inequalities on the same column as long |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
190 |
as the two inequalities provide an upper and lower bound on the column. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
191 |
For example, in this query: </p> <codeblock id="GUID-4EB94886-EDFF-58F2-8692-011A67AC5A60" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
192 |
SELECT d FROM demo315 WHERE a=5 AND b>11 AND b<23; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
193 |
</codeblock> <p>All three terms of the WHERE clause will be used because |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
194 |
the two inequalities on the “b” column provide an upper and lower |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
195 |
bound on the value of “b”. </p> <p>SQLite will only use the four inequalities |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
196 |
mentioned above to help constrain a search: “<”, “>”, “<=”, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
197 |
and “>=”. Other inequality operators such as not equal to (“!=” or |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
198 |
“<>”) and NOT NULL are not helpful to the query optimizer and will |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
199 |
never be used to control an index and help make the query run faster. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
200 |
<section id="GUID-CAD0C181-37E7-578A-A7E1-7843447C247F"><title>Use |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
201 |
Indexes To Help ORDER BY Clauses Evaluate Faster</title> <p>The default |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
202 |
method for evaluating an ORDER BY clause in a SELECT statement is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
203 |
to first evaluate the SELECT statement and store the results in a |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
204 |
temporary tables, then sort the temporary table according to the ORDER |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
205 |
BY clause and scan the sorted temporary table to generate the final |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
206 |
output. </p> <p>This method always works, but it requires three passes |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
207 |
over the data (one pass to generate the result set, a second pass |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
208 |
to sort the result set, and a third pass to output the results) and |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
209 |
it requires a temporary storage space sufficiently large to contain |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
210 |
the entire results set. </p> <p>Where possible, SQLite will avoid |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
211 |
storing and sorting the result set by using an index that causes the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
212 |
results to emerge from the query in sorted order in the first place. </p> <p>The way to get SQLite to use an index for sorting is to provide |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
213 |
an index that covers the same columns specified in the ORDER BY clause. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
214 |
For example, if the table and index are like this: </p> <codeblock id="GUID-F0103033-C5C8-5177-8AD7-70BCC45C33C9" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
215 |
CREATE TABLE demo316(a,b,c,data); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
216 |
CREATE INDEX idx316 ON demo316(a,b,c); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
217 |
</codeblock> <p>And you do a query like this: </p> <codeblock id="GUID-D67BB6FF-E213-5B86-A2C1-E1992DA96A62" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
218 |
SELECT data FROM demo316 ORDER BY a,b,c; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
219 |
</codeblock> <p>SQLite will use the idx316 index to implement the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
220 |
ORDER BY clause, obviating the need for temporary storage space and |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
221 |
a separate sorting pass. </p> <p>An index can be used to satisfy the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
222 |
search constraints of a WHERE clause and to impose the ORDER BY ordering |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
223 |
of outputs all at once. The trick is for the ORDER BY clause terms |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
224 |
to occur immediately after the WHERE clause terms in the index. For |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
225 |
example, one can write: </p> <codeblock id="GUID-02063968-34B5-5766-9D02-86D696D39C1E" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
226 |
SELECT data FROM demo316 WHERE a=5 ORDER BY b,c; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
227 |
</codeblock> <p>The “a” column is used in the WHERE clause and the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
228 |
immediately following terms of the index, “b” and “c” are used in |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
229 |
the ORDER BY clause. So in this case the idx316 index would be used |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
230 |
both to speed up the search and to satisfy the ORDER BY clause. </p> <p>This query also uses the idx316 index because, once again, the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
231 |
ORDER BY clause term “c” immediate follows the WHERE clause terms |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
232 |
“a” and “b” in the index: </p> <codeblock id="GUID-6760EC7E-E86A-5EBD-BDDD-32A68BE78A9E" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
233 |
SELECT data FROM demo316 WHERE a=5 AND b=17 ORDER BY c; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
234 |
</codeblock> <p>But now consider this: </p> <codeblock id="GUID-9363996C-8C30-5E04-B05F-392C8262F1F6" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
235 |
SELECT data FROM demo316 WHERE a=5 ORDER BY c; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
236 |
</codeblock> <p>Here there is a gap between the ORDER BY term “c” |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
237 |
and the WHERE clause term “a”. So the idx316 index cannot be used |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
238 |
to satisfy both the WHERE clause and the ORDER BY clause. The index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
239 |
will be used on the WHERE clause and a separate sorting pass will |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
240 |
occur to put the results in the correct order. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
241 |
<section id="GUID-109AF0DA-A054-504A-A432-76BD145B2AC4"><title>Add |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
242 |
Result Columns To The End Of Indexes</title> <p>Queries will sometimes |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
243 |
run faster if their result columns appear in the right-most entries |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
244 |
of an index. Consider the following example: </p> <codeblock id="GUID-63292052-B523-5671-B3EE-E10A66C7275F" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
245 |
CREATE TABLE demo317(a,b,c,data); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
246 |
CREATE INDEX idx317 ON demo316(a,b,c); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
247 |
</codeblock> <p>A query where all result column terms appears in the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
248 |
index, such as </p> <codeblock id="GUID-41F740E7-EAFC-583B-BFE6-E63DBEA354D7" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
249 |
SELECT c FROM demo317 WHERE a=5 ORDER BY b; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
250 |
</codeblock> <p>will typically run about twice as fast or faster than |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
251 |
a query that uses columns that are not in the index, e.g. </p> <codeblock id="GUID-098752F4-304A-5A84-834E-240D97D97C2D" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
252 |
SELECT data FROM demo317 WHERE a=5 ORDER BY b; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
253 |
</codeblock> <p>The reason for this is that when all information is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
254 |
contained within the index entry only a single search has to be made |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
255 |
for each row of output. But when some of the information is in the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
256 |
index and other parts are in the table, first there must be a search |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
257 |
for the appropriate index entry then a separate search is made for |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
258 |
the appropriate table row based on the RowID found in the index entry. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
259 |
Twice as much searching has to be done for each row of output generated. </p> <p>The extra query speed does not come for free, however. Adding |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
260 |
additional columns to an index makes the database file larger. So |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
261 |
when developing an application, the programmer will need to make a |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
262 |
space versus time trade-off to determine whether the extra columns |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
263 |
should be added to the index or not. </p> <p>Note that if any column |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
264 |
of the result must be obtained from the original table, then the table |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
265 |
row will have to be searched for anyhow. There will be no speed advantage, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
266 |
so you might as well omit the extra columns from the end of the index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
267 |
and save on storage space. The speed-up described in this section |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
268 |
can only be realized when every column in a table is obtainable from |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
269 |
the index. </p> <p>Taking into account the results of the previous |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
270 |
few sections, the best set of columns to put in an index can be described |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
271 |
as follows: </p> <ul> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
272 |
<li id="GUID-EBF4DEFB-2F5F-5D78-92FA-06FEAB0C3650"><p>The first columns |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
273 |
in the index should be columns that have equality constraints in the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
274 |
WHERE clause of the query. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
275 |
<li id="GUID-E5CB725C-6304-5946-9E18-E69B5F1A6A88"><p>The second group |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
276 |
of columns should match the columns specified in the ORDER BY clause. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
277 |
<li id="GUID-FBC00251-C3AD-5AC0-9102-EF66EA37DE4E"><p>Add additional |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
278 |
columns to the end of the index that are used in the result set of |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
279 |
the query. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
280 |
</ul> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
281 |
<section id="GUID-D7B5B389-E031-5512-8186-235B22F0D9C1"><title>Resolve |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
282 |
Indexing Ambiguities Using the Unary “+” Operator</title> <p>The SQLite |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
283 |
query optimizer usually does a good job of choosing the best index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
284 |
to use for a particular query, especially if ANALYZE has been run |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
285 |
to provide it with index performance statistics. But occasions do |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
286 |
arise where it is useful to give the optimizer hints. </p> <p>One |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
287 |
of the easiest ways to control the operation of the optimizer is to |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
288 |
disqualify terms in the WHERE clause or ORDER BY clause as candidates |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
289 |
for optimization by using the unary “+” operator. </p> <p>In SQLite, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
290 |
a unary “+” operator is a no-op. It makes no change to its operand, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
291 |
even if the operand is something other than a number. So you can always |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
292 |
prefix a “+” to an expression in without changing the meaning of the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
293 |
expression. As the optimizer will only use terms in WHERE, HAVING, |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
294 |
or ON clauses that have an index column name on one side of a comparison |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
295 |
operator, you can prevent such a term from being used by the optimizer |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
296 |
by prefixing the column name with a “+”. </p> <p>For example, suppose |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
297 |
you have a database with a schema like this: </p> <codeblock id="GUID-E7747EFD-FE58-5EA4-88B3-097C0A303F52" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
298 |
CREATE TABLE demo321(a,b,c,data); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
299 |
CREATE INDEX idx321a ON demo321(a); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
300 |
CREATE INDEX idx321b ON demo321(b); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
301 |
</codeblock> <p>If you issue a query such as this: </p> <codeblock id="GUID-87BD59FC-33A8-598B-B91F-607B26F7349D" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
302 |
SELECT data FROM demo321 WHERE a=5 AND b=11; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
303 |
</codeblock> <p>The query optimizer might use the “a=5” term with |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
304 |
idx321a or it might use the “b=11” term with the idx321b index. But |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
305 |
if you want to force the use of the idx321a index you can accomplish |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
306 |
that by disqualifying the second term of the WHERE clause as a candidate |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
307 |
for optimization using a unary “+” like this: </p> <codeblock id="GUID-E6EAB459-726A-5FE4-8065-6C46AC2C5B5C" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
308 |
SELECT data FROM demo321 WHERE a=5 AND +b=11; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
309 |
</codeblock> <p>The “+” in front of the “b=11” turns the left-hand |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
310 |
side of the equals comparison operator into an expression instead |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
311 |
of an indexed column name. The optimizer will then not recognize that |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
312 |
the second term can be used with an index and so the optimizer is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
313 |
compelled to use the first “a=5” term. </p> <p>The unary “+” operator |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
314 |
can also be used to disable ORDER BY clause optimizations. Consider |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
315 |
this query: </p> <codeblock id="GUID-0488D466-77B7-50E0-AB85-FF033A2D75DC" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
316 |
SELECT data FROM demo321 WHERE a=5 ORDER BY b; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
317 |
</codeblock> <p>The optimizer has the choice of using the “a=5” term |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
318 |
of the WHERE clause with idx321a to restrict the search. Or it might |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
319 |
choose to use do a full table scan with idx321b to satisfy the ORDER |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
320 |
BY clause and thus avoid a separate sorting pass. You can force one |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
321 |
choice or the other using a unary “+”. </p> <p>To force the use of |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
322 |
idx321a on the WHERE clause, add the unary “+” in from of the “b” |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
323 |
in the ORDER BY clause: </p> <codeblock id="GUID-E55A085F-D91F-58E0-B964-317BB3A9D7ED" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
324 |
SELECT data FROM demo321 WHERE a=5 ORDER BY +b; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
325 |
</codeblock> <p>To go the other way and force the idx321b index to |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
326 |
be used to satisfy the ORDER BY clause, disqualify the WHERE term |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
327 |
by prefixing with a unary “+”: </p> <codeblock id="GUID-D97EF52A-1F74-57EB-AC11-7911B4E088B3" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
328 |
SELECT data FROM demo321 WHERE +a=5 ORDER BY b; |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
329 |
</codeblock> <p>The reader is cautioned not to overuse the unary “+” |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
330 |
operator. The SQLite query optimizer usually picks the best index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
331 |
without any outside help. Premature use of unary “+” can confuse the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
332 |
optimizer and cause less than optimal performance. But in some cases |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
333 |
it is useful to be able override the decisions of the optimizer, and |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
334 |
the unary “+” operator is an excellent way to do this when it becomes |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
335 |
necessary. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
336 |
<section id="GUID-7BEBC49C-0528-5D58-9626-2A92F3D0D9E8"><title>Avoid |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
337 |
Indexing Large BLOBs and CLOBs</title> <p>SQLite stores indexes as |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
338 |
b-trees. Each b-tree node uses one page of the database file. In order |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
339 |
to maintain an acceptable fan-out, the b-tree module within SQLite |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
340 |
requires that at least 4 entries must fit on each page of a b-tree. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
341 |
There is also some overhead associated with each b-tree page. So at |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
342 |
the most there is about 250 bytes of space available on the main b-tree |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
343 |
page for each index entry. </p> <p>If an index entry exceeds this |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
344 |
allotment of approximately 250 bytes excess bytes are spilled to overflow |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
345 |
pages. There is no arbitrary limit on the number of overflow pages |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
346 |
or on the length of a b-tree entry, but for maximum efficiency it |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
347 |
is best to avoid overflow pages, especially in indexes. This means |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
348 |
that you should strive to keep the number of bytes in each index entry |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
349 |
below 250. </p> <p>If you keep the size of indexes significantly smaller |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
350 |
than 250 bytes, then the b-tree fan-out is increased and the binary |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
351 |
search algorithm used to search for entries in an index has fewer |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
352 |
pages to examine and therefore runs faster. So the fewer bytes used |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
353 |
in each index entry the better, at least from a performance perspective. </p> <p>For these reasons, it is recommended that you avoid indexing |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
354 |
large BLOBs and CLOBs. SQLite will continue to work when large BLOBs |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
355 |
and CLOBs are indexed, but there will be a performance impact. </p> <p>On the other hand, if you need to lookup entries using a large |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
356 |
BLOB or CLOB as the key, then by all means use an index. An index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
357 |
on a large BLOB or CLOB is not as fast as an index using more compact |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
358 |
data types such as integers, but it is still many order of magnitude |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
359 |
faster than doing a full table scan. So to be more precise, the advice |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
360 |
of this section is that you should design your applications so that |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
361 |
you do not need to lookup entries using a large BLOB or CLOB as the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
362 |
key. Try to arrange to have compact keys consisting of short strings |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
363 |
or integers. </p> <p>Note that many other SQL database engines disallow |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
364 |
the indexing of BLOBs and CLOBs in the first place. You simple cannot |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
365 |
do it. SQLite is more flexible that most in that it does allow BLOBs |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
366 |
and CLOBs to be indexed and it will use those indexes when appropriate. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
367 |
But for maximum performance, it is best to use smaller search keys. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
368 |
<section id="GUID-DD40F29F-DF93-536E-9B52-F9B9FF45155D"><title>Avoid |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
369 |
Excess Indexes</title> <p>Some developers approach SQL-based application |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
370 |
development with the attitude that indexes never hurt and that the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
371 |
more indexes you have, the faster your application will run. This |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
372 |
is definitely not the case. There is a costs associated with each |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
373 |
new index you create: </p> <ul> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
374 |
<li id="GUID-FD257BF7-F938-54B5-AC03-9536712D6281"><p>Each new index |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
375 |
takes up additional space in the database file. The more indexes you |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
376 |
have, the larger your database files will become for the same amount |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
377 |
of data. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
378 |
<li id="GUID-E1B74FB6-246A-5148-AF06-04E1B4B949F1"><p>Every INSERT |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
379 |
and UPDATE statement modifies both the original table and all indexes |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
380 |
on that table. So the performance of INSERT and UPDATE decreases linearly |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
381 |
with the number of indexes. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
382 |
<li id="GUID-56AAE2D1-71D6-5A23-8190-B0C80B204DED"><p>Compiling new |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
383 |
SQL statements using <codeph>Prepare()</codeph> takes longer when |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
384 |
there are more indexes for the optimizer to choose between. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
385 |
<li id="GUID-24B7F7D8-FAA9-5C78-B3C7-B886FA774C0B"><p>Surplus indexes |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
386 |
give the optimizer more opportunities to make a bad choice. </p> </li> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
387 |
</ul> <p>Your policy on indexes should be to avoid them wherever you |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
388 |
can. Indexes are powerful medicine and can work wonders to improve |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
389 |
the performance of a program. But just as too many drugs can be worse |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
390 |
than none at all, so also can too many indexes cause more harm than |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
391 |
good. </p> <p>When building a new application, a good approach is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
392 |
to omit all explicitly declared indexes in the beginning and only |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
393 |
add indexes as needed to address specific performance problems. </p> <p>Take care to avoid redundant indexes. For example, consider this |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
394 |
schema: </p> <codeblock id="GUID-89F20101-1628-5783-82B0-2ABE84078C7D" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
395 |
CREATE TABLE demo323a(a,b,c); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
396 |
CREATE INDEX idx323a1 ON demo323(a); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
397 |
CREATE INDEX idx323a2 ON demo323(a,b); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
398 |
</codeblock> <p>The idx323a1 index is redundant and can be eliminated. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
399 |
Anything that the idx323a1 index can do the idx323a2 index can do |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
400 |
better. </p> <p>Other redundancies are not quite as apparent as the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
401 |
above. Recall that any column or columns that are declared UNIQUE |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
402 |
or PRIMARY KEY (except for the special case of INTEGER PRIMARY KEY) |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
403 |
are automatically indexed. So in the following schema: </p> <codeblock id="GUID-2FE7B726-4027-518C-9217-B4BD1ECDA991" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
404 |
CREATE TABLE demo323b(x TEXT PRIMARY KEY, y INTEGER UNIQUE); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
405 |
CREATE INDEX idx323b1 ON demo323b(x); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
406 |
CREATE INDEX idx323b2 ON demo323b(y); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
407 |
</codeblock> <p>Both indexes are redundant and can be eliminated with |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
408 |
no loss in query performance. Occasionally one sees a novice SQL programmer |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
409 |
use both UNIQUE and PRIMARY KEY on the same column: </p> <codeblock id="GUID-CDE12649-BDB4-58D4-8981-02628BDF5C79" xml:space="preserve"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
410 |
CREATE TABLE demo323c(p TEXT UNIQUE PRIMARY KEY, q); |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
411 |
</codeblock> <p>This has the effect of creating two indexes on the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
412 |
“p” column – one for the UNIQUE keywords and another for the PRIMARY |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
413 |
KEY keyword. Both indexes are identical so clearly one can be omitted. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
414 |
A PRIMARY KEY is guaranteed to always be unique so the UNIQUE keyword |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
415 |
can be removed from the demo323c table definition with no ambiguity |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
416 |
or loss of functionality. </p> <p>It is not a fatal error to create |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
417 |
too many indexes or redundant indexes. SQLite will continue to generate |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
418 |
the correct answers but it may take longer to produce those answers |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
419 |
and the resulting database files might be a little larger. So for |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
420 |
best results, keep the number of indexes to a minimum. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
421 |
<section id="GUID-9337E315-BB5A-56D0-8319-6C398D26151F"><title>Avoid |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
422 |
Tables and Indexes with an Excessive Number of Columns</title> <p>SQLite places no arbitrary limits on the number of columns in a table |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
423 |
or index. There are known commercial applications using SQLite that |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
424 |
construct tables with tens of thousands of columns each. And these |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
425 |
applications actually work. </p> <p>However the database engine is |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
426 |
optimized for the common case of tables with no more than a few dozen |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
427 |
columns. For best performance you should try to stay in the optimized |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
428 |
region. Furthermore, we note that relational databases with a large |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
429 |
number of columns are usually not well normalized. So even apart from |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
430 |
performance considerations, if you find your design has tables with |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
431 |
more than a dozen or so columns, you really need to rethink how you |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
432 |
are building your application. </p> <p>There are a number of places |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
433 |
in <codeph>Prepare()</codeph> that run in time O(N<sup>2</sup>) where |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
434 |
N is the number of columns in the table. The constant of proportionality |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
435 |
is small in these cases so you should not have any problems for N |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
436 |
of less than one hundred but for N on the order of a thousand, the |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
437 |
time to run <codeph>Prepare()</codeph> can start to become noticeable. </p> <p>When the bytecode is running and it needs to access the i-th |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
438 |
column of a table, the values of the previous i-1 columns must be |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
439 |
accessed first. So if you have a large number of columns, accessing |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
440 |
the last column can be an expensive operation. This fact also argues |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
441 |
for putting smaller and more frequently accessed columns early in |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
442 |
the table. </p> <p>There are certain optimizations that will only |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
443 |
work if the table has 30 or fewer columns. The optimization that extracts |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
444 |
all necessary information from an index and never refers to the underlying |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
445 |
table works this way. So in some cases, keeping the number of columns |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
446 |
in a table at or below 30 can result in a 2-fold speed improvement. </p> <p>Indexes will only be used if they contain 30 or fewer columns. |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
447 |
You can put as many columns in an index as you want, but if the number |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
448 |
is greater than 30, the index will never improve performance and will |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
449 |
never do anything but take up space in your database file. </p> </section> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
450 |
</conbody><related-links> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
451 |
<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
452 |
Overview</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
453 |
<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
454 |
Server Guide</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
455 |
<link href="GUID-E51836E1-D33E-506C-B75B-19B8E3CC313A.dita"><linktext>SQLite</linktext> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
456 |
</link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
457 |
<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
458 |
DB Overview</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
459 |
<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
460 |
Transient Tables</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
461 |
<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
462 |
Datafile Corruption</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
463 |
<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
464 |
<linktext>SQL Insertion Tips</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
465 |
<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
466 |
Schema Tips</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
467 |
<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
468 |
Expressions</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
469 |
<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
470 |
Statement Tips</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
471 |
<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
472 |
Joins</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
473 |
<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
474 |
Command</linktext></link> |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
475 |
<link href="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217.dita"><linktext> SQL |
48780e181b38
Week 28 contribution of SDK documentation content. See release notes for details. Fixes bugs Bug 1897 and Bug 1522.
Dominic Pinkman <dominic.pinkman@nokia.com>
parents:
7
diff
changeset
|
476 |
WHERE CLause Tips</linktext></link> |
7
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
477 |
</related-links></concept> |