INDEX

Adding INDEX to a table might speed up certain request.

.timer on
.import --csv -v examples/big-data.csv pairs

SELECT * from pairs WHERE name = 'aaaafryc'; -- 'aaaaaaaz';

CREATE INDEX names ON pairs (name);

SELECT * from pairs WHERE name = 'aaaafryc'; -- 'aaaaaaaz';

$ sqlite3 < examples/big-data-time.sql

Column separator ",", row separator "\n"
CREATE TABLE "main"."pairs"(
"name" ANY, "value" ANY)

Added 100000 rows with 0 errors using 100001 lines of input
aaaafryc|99999
Run Time: real 0.005019 user 0.005032 sys 0.000000
Run Time: real 0.027671 user 0.022654 sys 0.005020
aaaafryc|99999
Run Time: real 0.000018 user 0.000018 sys 0.000002

• The 1st SELECT takes 0.005019 seconds. That’s going over all the rows.

• The 3rd SELECT takes 0.000018 seconds. That’s because having and index allows SQLite to go directly to the right row.

• The CREATE INDEX statement takes 0.027671 seconds. So it is very time consuming and also uses extra memory. It starts to be worth doing it if you run the select at least 7 times. (In this specific case.)