SQL for the IoT Move the query to the data, not the data to the - - PowerPoint PPT Presentation

SQL for the IoT

Move the query to the data, not the data to the query.

Internals

Why Do You Care?

• So you can better understand what your query is

doing.

• So that you can appreciate how much work the

database is saving you.

• So that you can fix things when they go wrong.
• So that you can write better SQL that runs faster

and uses less memory and disk space.

About These Notes

• Nothing in these notes is required

–to write applications that use SQL and/or SQLite –to query an SQL and/or SQLite database –to maintain or enhance SQL and/or SQLite-based

software

• Everything in these notes is required in order to

be an SQL and/or SQLite guru.

Key Concept

• SQL is a peculiar programming language

–Each SQL statement is a separate program –SQL describes what instead of how

• An RDBMS consists of...

–Compiler to translate SQL into procedures –Virtual Machine to evaluate the procedures

Example

SELECT * FROM table1;

Translates into:

Open database file containing table1 Rewind the file while not at end-of-file read all columns out of current record return the columns to the caller advance file to the next record end-while close the file

Imagine what this translates into:

SELECT eqptid, enclosureid FROM eqpt WHERE typeid IN ( SELECT typeid FROM typespec WHERE attrid=( SELECT attrid FROM attribute WHERE name='detect_autoactuate' ) AND value=1 INTERSECT SELECT typeid FROM typespec WHERE attrid=( SELECT attrid FROM attribute WHERE name='algorithm' ) AND value IN ('sensor','wetbulb') )

Or This....

SELECT h.url, h.title, f.url, (SELECT b.parent FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 WHERE b.type = 1 AND b.fk = h.id ORDER BY b.lastModified DESC LIMIT 1) AS parent, (SELECT b.title FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 WHERE b.type = 1 AND b.fk = h.id ORDER BY b.lastModified DESC LIMIT 1) AS bookmark, (SELECT GROUP_CONCAT(t.title, ',') FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = b.parent AND t.parent = ?1 WHERE b.type = 1 AND b.fk = h.id) AS tags, h.visit_count, h.typed, h.frecency FROM moz_places_temp h LEFT OUTER JOIN moz_favicons f ON f.id = h.favicon_id WHERE h.frecency <> 0 UNION ALL SELECT h.url, h.title, f.url, (SELECT b.parent FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 WHERE b.type = 1 AND b.fk = h.id ORDER BY b.lastModified DESC LIMIT 1) AS parent, (SELECT b.title FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 WHERE b.type = 1 AND b.fk = h.id ORDER BY b.lastModified DESC LIMIT 1) AS bookmark, (SELECT GROUP_CONCAT(t.title, ',') FROM moz_bookmarks b JOIN moz_bookmarks t ON t.id = b.parent AND t.parent = ?1 WHERE b.type = 1 AND b.fk = h.id) AS tags, h.visit_count, h.typed, h.frecency FROM moz_places h LEFT OUTER JOIN moz_favicons f ON f.id = h.favicon_id WHERE h.id NOT IN (SELECT id FROM moz_places_temp) AND h.frecency <> 0 ORDER BY 9 DESC

The Whole Point Of SQL...

• A few lines of SQL generates the equivalent of

hundreds or thousands of lines of procedural code.

• By adding an index, entirely new procedures are

used without recoding.

• The SQL Query Optimizer is tasked with picking

the algorithm

– so that you, the developer, don't have to

Ins and Outs of SQL

Compile SQL into a program Run the program SQL

Prep'ed Stmt

Result

Front Half Back Half

Ins and Outs of SQL

Compile SQL into a program Run the program SQL

Prep'ed Stmt

Result

sqlite3_prepare_v2( ) sqlite3_step() sqlite3_stmt object

Ins and Outs of SQL

Compile SQL into a program Run the program SQL

Prep'ed Stmt

Result

sqlite3_prepare_v2( ) sqlite3_step() sqlite3_exec(){ sqlite3_prepare_v2(); while( sqlite3_step()!=DONE ){}; sqlite3_finalize(); }

Architecture Of SQLite

Virtual Machine B-Tree Pager OS Interface Parser Code Generator

Virtual Machine B-Tree Pager OS Interface Compile SQL into a program Run the program SQL

Prep'ed Stmt

Result

Parser Code Generator

Virtual Machine B-Tree Pager OS Interface Compile SQL into a program Run the program SQL

Prep'ed Stmt

Result

Parser Code Generator

Virtual Machine B-Tree Pager OS Interface Compile SQL into a program Run the program SQL

Prep'ed Stmt

Result

Parser Code Generator

• “Storage Engine”
• Fast, transactional,
• rdered, key/value store

Architecture Of SQLite

Parser Code Generator Virtual Machine B-Tree Pager OS Interface

• Byte code interpreter
• Big switch statement

inside a for loop.

• Other engines walk a

tree of structures

• Similar to JVM or Parrot
• r P-Code

addr opcode p1 p2 p3 p4 p5 comment

• 0 Init 0 12 0 00 Start at 12

1 OpenRead 0 2 0 3 00 root=2 iDb=0; tab 2 Explain 0 0 0 SCAN TABLE tab 00 3 Rewind 0 10 0 00 4 Column 0 0 1 00 r[1]=tab.Fruit 5 Ne 2 9 1 (BINARY) 69 if r[2]!=r[1] goto 9 6 Column 0 2 3 00 r[3]=tab.Price 7 RealAffinity 3 0 0 00 8 ResultRow 3 1 0 00 output=r[3] 9 Next 0 4 0 01 10 Close 0 0 0 00 11 Halt 0 0 0 00 12 Transaction 0 0 1 0 01 13 TableLock 0 2 0 tab 00 iDb=0 root=2 write=0 14 String8 0 2 0 Orange 00 r[2]='Orange' 15 Goto 0 1 0 00

EXPLAIN SELECT price FROM tab WHERE fruit='Orange'

addr opcode p1 p2 p3 p4 p5 comment

• 0 Init 0 12 0 00 Start at 12

1 OpenRead 0 2 0 3 00 root=2 iDb=0; tab 2 Explain 0 0 0 SCAN TABLE tab 00 3 Rewind 0 10 0 00 4 Column 0 0 1 00 r[1]=tab.Fruit 5 Ne 2 9 1 (BINARY) 69 if r[2]!=r[1] goto 9 6 Column 0 2 3 00 r[3]=tab.Price 7 RealAffinity 3 0 0 00 8 ResultRow 3 1 0 00 output=r[3] 9 Next 0 4 0 01 10 Close 0 0 0 00 11 Halt 0 0 0 00 12 Transaction 0 0 1 0 01 13 TableLock 0 2 0 tab 00 iDb=0 root=2 write=0 14 Variable 1 2 0 ?1 00 r[2]=parameter(1,?1) 15 Goto 0 1 0 00

EXPLAIN SELECT price FROM tab WHERE fruit=?1

Architecture Of SQLite

Parser Code Generator Virtual Machine B-Tree Pager OS Interface

• Ordered key/value pairs

with unique keys

• O(logN) insert, seek,

and delete

• O(1) next and previous

Architecture Of SQLite

Parser Code Generator Virtual Machine B-Tree Pager OS Interface

• Atomic commit and

rollback

• Uniform size pages

numbered from 1

• No interpretation of

page content

• Cache

Architecture Of SQLite

Parser Code Generator Virtual Machine B-Tree Pager OS Interface

• Platform-specific

interface to the OS

• Run-time changeable
• Portability layer

Logical View of SQL Table Storage

64bit integer key “rowid” Arbitrary length data in “record” format

Variable Length Integers

1xxxxxxx - high bit set. 7 bits of data 0xxxxxxx - high bit clear. 7 bits of data xxxxxxxx - 8 bits of data 128 to 16383 2097152 to 268435455 34359738368 to 4398046511103 562949953421312 to 72057594037927935 0 to 127 16384 to 2097151 268435456 to 34359738367 4398046511104 to 562949953421311 Less than 0 or greater than 72057594037927935

✔ Small positive ROWIDs stored more

efficiently

B+tree Structure

(used by SQL tables)

Root page Leaf pages Integer key Pointer to lower page Binary content

B+tree Structure

(used by SQL tables)

Non-leaf pages hold only keys

• Key + Data in leaves
• Combined key+data is a “cell”
• As few as one “cell” per page.

Between 50 and 8000 keys/page depending

• n page size.

Integer key Pointer to lower page Binary content Some keys appear more than

• nce in the tree.

Mapping B-trees Into Pages

Page 2 Page 3 Page 4 Page 1 Page 5 Page 6 Page 7

To see how pages are used:

• make showdb
• ./showdb database.db pgidx

Available pages: 1..1146 1: root leaf of table [sqlite_master] 2: root interior node of table [blob] 3: root interior node of index [sqlite_autoindex_blob_1] 4: root interior node of table [delta] 5: root interior node of table [rcvfrom] 6: root leaf of index [sqlite_autoindex_rcvfrom_1] 7: root leaf of table [config] 8: root leaf of index [sqlite_autoindex_config_1] 9: root leaf of table [shun] 10: root leaf of index [sqlite_autoindex_shun_1] 11: root leaf of table [private] ... 264: leaf of table [blob], child 201 of page 2 265: leaf of table [blob], child 202 of page 2 266: overflow 1 from cell 0 of page 268 267: overflow 2 from cell 0 of page 268 268: leaf of table [blob], child 203 of page 2 ...

Overflow

Integer key Pointer to another page Binary content

Implications Of Overflow

• Move small and

common fields near the beginning.

• Put larger and

infrequently accessed fields at the end.

• Move large BLOBs to a

separate table and access via join.

Logical View of SQL Index Storage

Arbitrary length key in “record” format Zero data

B-tree Structure

(used by indexes & WITHOUT ROWID tables)

• Key only. No data. The key is the data.
• Larger binary keys, hence lower fan-out
• Each key appears in the table only once

Binary key Pointer to lower page

sqlite_master

CREATE TABLE sqlite_master( type text, name text, tbl_name text, rootpage integer, sql text );

✔ sqlite_master always rooted at page 1

sqlite_master

sqlite> CREATE TABLE t1(x); sqlite> .mode line sqlite> SELECT * FROM sqlite_master; type = table name = t1 tbl_name = t1 rootpage = 2 sql = CREATE TABLE t1(x)

Virtual Machine

• Byte code interpreter
• Defines the “record

format”

Content of tables Key of indices Record Format

Parser Code Generator Virtual Machine B-Tree Pager OS Interface

Record Format

header size type 1 type 2 ... type N data 1 data 2 ... data N Variable length integers type-dependent content Heade r Dat a

Integer Type Codes

Record Format Example

CREATE TABLE t1(a,b,c); INSERT INTO t1 VALUES(177, NULL, 'hello');

mac02:bld drh$ sqlite3 test.db SQLite version 3.8.5 2014-06-24 20:19:21 Enter ".help" for usage hints. sqlite> CREATE TABLE t1(a,b,c); sqlite> INSERT INTO t1 VALUES(177, NULL, 'hello'); sqlite> .q mac02:bld drh$ showdb test.db 2bd Pagesize: 1024 Available pages: 1..2 Header on btree page 2: 000: 0d 13 table leaf 001: 00 00 0 Offset to first freeblock 003: 00 01 1 Number of cells on this page 005: 03 f3 1011 Offset to cell content area 007: 00 0 Fragmented byte count Cell[0]: 3f3: 0b payload-size: 11 3f4: 01 rowid: 1 3f5: 04 record-header-size: 4 3f6: 02 typecode[0]: 2 - int16 3f7: 00 typecode[1]: 0 - NULL 3f8: 17 typecode[2]: 23 - text(5) 3f9: 00 b1 data[0]: 177 3fb: 68 65 6c 6c 6f data[2]: 'hello' mac02:bld drh$

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL );

Key Data rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Peach'

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE rowid=4

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

CREATE INDEX idx1 ON tab(fruit)

1:1

Key Data Key

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Peach'

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

SELECT price FROM tab WHERE fruit='Orange'

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

CREATE INDEX idx2 ON tab(state) idx2 tab

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d 1 2 4 5 18 19 23 state

FL NC SC CA FL NC CA

Key Data Key

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d 1 2 4 5 18 19 23 state

FL NC SC CA FL NC CA

idx2 tab SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

idx3 tab

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

CREATE INDEX idx3 ON tab(fruit, state)

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

Key Data Key

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

CREATE INDEX idx3 ON tab(fruit, state)

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

FL NC SC CA FL NC CA

CREATE INDEX idx3 ON tab(fruit, state)

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

state

FL NC SC CA FL NC CA

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

idx5 tab

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

CREATE INDEX idx5 ON tab(fruit, state, price)

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

Key Data Key

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

1

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

2

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d 1 2 4 5 18 19 23 state

FL NC SC CA FL NC CA

idx2 tab SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

3

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

state

FL NC SC CA FL NC CA

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

4

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Orange' AND state='CA'

5

SELECT price FROM tab WHERE fruit='Orange' OR state='CA'

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT price FROM tab WHERE fruit='Orange' OR state='CA'

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

SELECT price FROM tab WHERE fruit='Orange' OR state='CA'

rowi d 1 2 4 5 18 19 23 state

FL NC SC CA FL NC CA

UNION

6

idx4 tab

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

CREATE INDEX idx4 ON tab(state,fruit)

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

Key Data Key

idx4 tab

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

SELECT price FROM tab WHERE fruit='Orange'

7

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit

sorte r

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY rowid

SELECT * FROM tab ORDER BY rowid DESC

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit LIMIT 2

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit LIMIT 2

sorte r

SELECT * FROM tab ORDER BY fruit DESC

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit

SELECT * FROM tab ORDER BY fruit, state

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit DESC, state DESC

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab ORDER BY fruit, state DESC

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

SELECT * FROM tab WHERE fruit='Orange' ORDER BY state

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL );

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

CREATE TABLE tab( Rowid INTEGER PRIMARY KEY, Fruit TEXT, State TEXT, Price REAL );

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, PRIMARY KEY(Fruit, State) );

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

Key Data Key

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, PRIMARY KEY(Fruit, State) ); CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, UNIQUE(Fruit, State) ); CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL ); CREATE UNIQUE INDEX idx ON tab(Fruit,State);

All mean the same thing (to SQLite)

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, PRIMARY KEY(Fruit, State) ) WITHOUT ROWID;

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

Key Data

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, PRIMARY KEY(Fruit, State) );

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

state

NC SC CA FL NC

rowi d fruit 2 4 5 18 19

Apple Peach Grape Lemon Strawberr y FL CA

1 23

Orange Orange

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

With Rowid: Without Rowid:

CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, Amt INT, PRIMARY KEY(Fruit, State) ) WITHOUT ROWID; CREATE INDEX tab_amt ON tab(amt); state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

amt

375 1000 138 200 750 980 825

Key Data Key

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

amt

375 1000 138 200 750 980 825 CREATE TABLE tab( Price REAL, State TEXT, Fruit TEXT, Amt INT, PRIMARY KEY(Fruit, State) ) WITHOUT ROWID;

Same As CREATE TABLE tab( Fruit TEXT, State TEXT, Price REAL, Amt INT, PRIMARY KEY(Fruit, State) ) WITHOUT ROWID; CREATE INDEX tab_amt ON tab(amt); Key Data Key

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

amt

375 1000 138 200 750 980 825

SELECT price FROM tab WHERE fruit='Peach';

rowi d fruit state price 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange FL NC SC CA FL NC CA 0.85 0.45 0.60 0.80 1.25 2.45 1.05

rowi d fruit 1 2 4 5 18 19 23

Orange Apple Peach Grape Lemon Strawberr y Orange

SELECT price FROM tab WHERE fruit='Peach';

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

amt

375 1000 138 200 750 980 825

SELECT fruit, state FROM tab WHERE amt<250;

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

amt

375 1000 138 200 750 980 825

state

NC SC CA FL NC

fruit

Apple Peach Grape Lemon Strawberr y FL CA Orange Orange

price

0.85 0.45 0.60 0.80 1.25 2.45 1.05

amt

375 1000 138 200 750 980 825

SELECT fruit, state, price FROM tab WHERE amt<250;

When To Use WITHOUT ROWID

• Non-INTEGER and/or composite PRIMARY

KEYs

• No need for AUTOINCREMENT, Direct BLOB I/O,
• r the update hook
• Average row size less than 1/20th of page size

–Check this using sqlite3_analyzer.exe

• Run experiments to see if it helps

Index Column Order

SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y; CREATE INDEX t1i1 ON t1(w,x,y,z);

Index Column Order

SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y; CREATE INDEX t1i1 ON t1(w,x,y,z);

WHERE clause equality terms first

Index Column Order

SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y; CREATE INDEX t1i1 ON t1(w,x,y,z);

ORDER BY terms second. No gaps! Can overlap WHERE clause.

Index Column Order

SELECT x, y, z FROM t1 WHERE w=5 AND x=6 ORDER BY x, y; CREATE INDEX t1i1 ON t1(w,x,y,z);

Result set columns can appear anywhere and in any order.

Index Column Order

SELECT * FROM t1 WHERE w=5 AND x>=6 AND y=23; CREATE INDEX t1i1 ON t1(w,x,y,z);

Equality terms first Range constraints at the end Terms past the range constraint are not usable for search.

Avoid Redundant Indices

CREATE TABLE t2(x,y,z); CREATE INDEX t2i1 ON t2(x,y,z); CREATE INDEX t2i2 ON t2(x,y); CREATE INDEX t2i3 ON t2(x);

Avoiding & Fixing Planner Problems

• Create appropriate indexes
• Avoid low-quality indexes
• Run ANALYZE
• Instrument your code
• Use unlikely() and likelihood() hints
• Use CROSS JOIN to force loop nesting order
• Disable WHERE terms using unary “+”
• INDEXED BY to force a particular index

Query Plan Stability Guarantee

SQLite will always generate the same query plan for the same SQL text provided: 1) The schema is not changed 2) You do not rerun ANALYZE 3) Omit SQLITE_ENABLE_STAT[34] 4) Keep the same version of SQLite