E7RbuHxf

1danya@danya-GE72MVR-7RG:/tmp$ sqlite3 test.db 
2SQLite version 3.31.1 2020-01-27 19:55:54
3Enter ".help" for usage hints.
4sqlite> .timer on
5sqlite> .schema
6CREATE TABLE IF NOT EXISTS "noindex" ("primary_key" INTEGER NOT NULL PRIMARY KEY, "number" INTEGER NOT NULL, "string" VARCHAR(255) NOT NULL);
7CREATE TABLE IF NOT EXISTS "yesindex" ("primary_key" INTEGER NOT NULL PRIMARY KEY, "number" INTEGER NOT NULL, "string" VARCHAR(255) NOT NULL);
8CREATE INDEX "yesindex_number" ON "yesindex" ("number");
9CREATE INDEX "yesindex_string" ON "yesindex" ("string");
10sqlite> select count(1) from noindex;
111000000
12Run Time: real 0.075 user 0.036240 sys 0.037875
13sqlite> select count(1) from yesindex;
141000000
15Run Time: real 0.024 user 0.015581 sys 0.008251
16sqlite> select * from noindex where primary_key=123456;
17Run Time: real 0.001 user 0.000088 sys 0.000103
18sqlite> select * from noindex where primary_key=-123456;
19-123456|471|7b4d4c34-a9a6-460f-890c-c642cf511b34
20Run Time: real 0.001 user 0.000112 sys 0.000130
21sqlite> select * from yesindex where primary_key=-123456;
22-123456|815|7bfa0c2f-ded9-4d95-bffa-93cb06895f8e
23Run Time: real 0.000 user 0.000121 sys 0.000151
24sqlite> 
25sqlite> 
26sqlite> explain query plan select count(1) from noindex where number=13;
27QUERY PLAN
28`--SCAN TABLE noindex
29Run Time: real 0.000 user 0.000068 sys 0.000084
30sqlite> select count(1) from noindex where number=13;
31957
32Run Time: real 0.104 user 0.079170 sys 0.024964
33sqlite> explain query plan select count(1) from yesindex where number=13;
34QUERY PLAN
35`--SEARCH TABLE yesindex USING COVERING INDEX yesindex_number (number=?)
36Run Time: real 0.000 user 0.000101 sys 0.000071
37sqlite> select count(1) from yesindex where number=13;
381035
39Run Time: real 0.001 user 0.000209 sys 0.000147
40sqlite> 
41sqlite> 
42sqlite> explain query plan select * from noindex where string='...';
43QUERY PLAN
44`--SCAN TABLE noindex
45Run Time: real 0.000 user 0.000109 sys 0.000078
46sqlite> explain query plan select * from yesindex where string='...';
47QUERY PLAN
48`--SEARCH TABLE yesindex USING INDEX yesindex_string (string=?)
49Run Time: real 0.000 user 0.000195 sys 0.000000
50sqlite> select * from noindex where string = '0de9426d-9f7d-4afd-bb3c-6ac1c472a8a3'; -- value found externally; we know there is only one such row, but the database doesn't
51999986|510|0de9426d-9f7d-4afd-bb3c-6ac1c472a8a3
52Run Time: real 0.112 user 0.084981 sys 0.026457
53sqlite> select * from yesindex where string = '2ef1b5bd-0cad-49fb-a83a-31a613f4cc0f';
54999785|83|2ef1b5bd-0cad-49fb-a83a-31a613f4cc0f
55Run Time: real 0.000 user 0.000068 sys 0.000043
56sqlite> 
57sqlite> 
58sqlite> explain query plan select count(1) from noindex where string like '0%';
59QUERY PLAN
60`--SCAN TABLE noindex
61Run Time: real 0.001 user 0.000125 sys 0.000079
62sqlite> select count(1) from noindex where string like '0%';
6362788
64Run Time: real 0.121 user 0.089674 sys 0.031061
65sqlite> explain query plan select count(1) from yesindex where string like '0%';
66QUERY PLAN
67`--SCAN TABLE yesindex
68Run Time: real 0.001 user 0.000115 sys 0.000066
69sqlite> select count(1) from yesindex where string like '0%';
7062502
71Run Time: real 0.120 user 0.084405 sys 0.035334
72sqlite> 
73sqlite> 
74sqlite> 
75sqlite> .output /dev/null
76sqlite> explain query plan select * from noindex order by number limit 100;
77Run Time: real 0.000 user 0.000106 sys 0.000059
78sqlite> .output /dev/stdout
79sqlite> explain query plan select * from noindex order by number limit 100;
80QUERY PLAN
81|--SCAN TABLE noindex
82`--USE TEMP B-TREE FOR ORDER BY
83Run Time: real 0.000 user 0.000131 sys 0.000072
84sqlite> select * from noindex order by number limit 5;
85-997021|0|50cfb954-c90e-43e7-9175-398c78759e1e
86-996890|0|d0e4ae31-a13d-4433-ada5-29dcbd1b58dc
87-996552|0|7605531a-56ca-44e1-aba2-552afced019b
88-994728|0|6d5c2640-0a92-4efc-8551-1f2a8bda8ae1
89-993391|0|80c81d45-c1f1-4ce7-adbe-a9520b5a6471
90Run Time: real 0.127 user 0.100052 sys 0.026019
91sqlite> 
92sqlite> 
93sqlite> 
94sqlite> explain query plan select * from yesindex order by number limit 5;
95QUERY PLAN
96`--SCAN TABLE yesindex USING INDEX yesindex_number
97Run Time: real 0.000 user 0.000119 sys 0.000060
98sqlite> select * from yesindex order by number limit 5;
99-996630|0|2ffcc550-7154-49d5-b340-81e9f8361ea5
100-996103|0|3f0925be-3144-4d26-bc11-baab0b84db52
101-995480|0|27503512-bfc9-4f63-9e14-6ef604f347b2
102-991061|0|d58adadb-5539-4bd7-b807-f950295ddc89
103-989118|0|b3957d57-fdf2-436e-bb6d-dc36260ac7ee
104Run Time: real 0.000 user 0.000263 sys 0.000134
105sqlite> 
106sqlite> 
107sqlite> 
108sqlite> explain query plan select * from yesindex order by number, string limit 5;
109QUERY PLAN
110|--SCAN TABLE yesindex USING INDEX yesindex_number
111`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
112Run Time: real 0.000 user 0.000148 sys 0.000075
113sqlite> select * from yesindex order by number, string limit 5;
114827100|0|0018ffcc-b523-46ff-98ab-da48d158141b
115-213812|0|00270949-f8b3-493c-9b9d-3b6b4c9c0bfa
116118590|0|00c07705-a8da-4fea-9d96-923bae92fe0b
117809|0|012595d4-a20e-4de5-872a-49406ab48755
118-387694|0|0173ce15-c892-4d5e-b5d0-1129413a39c8
119Run Time: real 0.007 user 0.000000 sys 0.007220
120sqlite> 
121sqlite> 
122sqlite> explain query plan select * from noindex order by number, string limit 5;
123QUERY PLAN
124|--SCAN TABLE noindex
125`--USE TEMP B-TREE FOR ORDER BY
126Run Time: real 0.000 user 0.000132 sys 0.000068
127sqlite> select * from noindex order by number, string limit 5;
128-594360|0|004ae389-5663-466f-b8a5-e2989fa96bd8
129-16872|0|00a70626-5c6d-4b39-bf1a-48739f3ec9de
130-781633|0|00b3ba02-0b07-4b72-ab57-89711a2c66e3
131313447|0|00e5353c-f713-401f-906d-5bfa07e53b19
132-124934|0|00f65038-a48e-43f0-9269-b3aee30b6657
133Run Time: real 0.118 user 0.106587 sys 0.010582
134sqlite> 
135
136