SQLite Forum

Performance Issue: How can I increase a performance?
Login
I've changed the table schema

## Table Definition
```
CREATE TABLE item (
    date_time TIMESTAMP NOT NULL,
    item_id INTEGER NOT NULL,
    list_id BLOB(16) NOT NULL,
    modified TIMESTAMP,
    unique_id BLOB(16),
    guid BLOB(16),
    parent_guid BLOB(16),
    object_type INTEGER,
    author_id INTEGER,
    editor_id INTEGER,
    created TIMESTAMP,
    folder VARCHAR(128),
    type VARCHAR(128),
    file_leaf_ref VARCHAR(64),
    uri VARCHAR(255),
    content_type VARCHAR(128),
    file_ref VARCHAR(128),
    title VARCHAR(128),
    etag INTEGER,
    related_document VARCHAR(64),
    server_redirect VARCHAR(64),
    relocated INTEGER(1) DEFAULT 0,
    tax_keyword VARCHAR,
    deleted_date_time TIMESTAMP,
    unrecognized_attributes JSON
)
```


## EXPLAIN

```
sqlite> SELECT *
   ...> FROM item
   ...> WHERE list_id='3ef58f02-5f41-43b6-890d-091afb4738eb' AND item_id=70 AND modified=1592085845000;
QUERY PLAN
`--SEARCH TABLE item USING INDEX idx_item_item_id_and_modified (item_id=? AND modified=?)
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     38    0                    00  Start at 38
1     OpenRead       0     2     0     25             00  root=2 iDb=0; item
2     OpenRead       1     5     0     k(3,,,)        02  root=5 iDb=0; idx_item_item_id_and_modified
3     Integer        70    1     0                    00  r[1]=70
4     Int64          0     2     0     1592085845000  00  r[2]=1592085845000
5     SeekGE         1     37    1     2              00  key=r[1..2]
6       IdxGT          1     37    1     2              00  key=r[1..2]
7       DeferredSeek   1     0     0                    00  Move 0 to 1.rowid if needed
8       Column         0     2     3                    00  r[3]=item.list_id
9       Ne             4     36    3     (BINARY)       51  if r[3]!=r[4] goto 36
10      Column         0     0     5                    00  r[5]=item.date_time
11      Column         1     0     6                    00  r[6]=item.item_id
12      Column         0     2     7                    00  r[7]=item.list_id
13      Column         1     1     8                    00  r[8]=item.modified
14      Column         0     4     9                    00  r[9]=item.unique_id
15      Column         0     5     10                   00  r[10]=item.guid
16      Column         0     6     11                   00  r[11]=item.parent_guid
17      Column         0     7     12                   00  r[12]=item.object_type
18      Column         0     8     13                   00  r[13]=item.author_id
19      Column         0     9     14                   00  r[14]=item.editor_id
20      Column         0     10    15                   00  r[15]=item.created
21      Column         0     11    16                   00  r[16]=item.folder
22      Column         0     12    17                   00  r[17]=item.type
23      Column         0     13    18                   00  r[18]=item.file_leaf_ref
24      Column         0     14    19                   00  r[19]=item.uri
25      Column         0     15    20                   00  r[20]=item.content_type
26      Column         0     16    21                   00  r[21]=item.file_ref
27      Column         0     17    22                   00  r[22]=item.title
28      Column         0     18    23                   00  r[23]=item.etag
29      Column         0     19    24                   00  r[24]=item.related_document
30      Column         0     20    25                   00  r[25]=item.server_redirect
31      Column         0     21    26    0              00  r[26]=item.relocated
32      Column         0     22    27                   00  r[27]=item.tax_keyword
33      Column         0     23    28                   00  r[28]=item.deleted_date_time
34      Column         0     24    29                   00  r[29]=item.unrecognized_attributes
35      ResultRow      5     25    0                    00  output=r[5..29]
36    Next           1     6     1                    00
37    Halt           0     0     0                    00
38    Transaction    0     0     4     0              01  usesStmtJournal=0
39    String8        0     4     0     3ef58f02-5f41-43b6-890d-091afb4738eb  00  r[4]='3ef58f02-5f41-43b6-890d-091afb4738eb'
40    Goto           0     1     0                    00
```