Clever Geek Handbook

Sqlite3 is about 6X slower than grep

I have a table with 188 million rows and a text file with 188 million rows as shown below:

CREATE TABLE trigram (count integer, A text, B text, C text)
time echo 'select * from trigram where C="mailman";'|sqlite3 3g.db
18.419 seconds.
time grep 'mailman$' N-Grams/3g
3.137 seconds

or a more complex query like

time grep 'the [^ ]* mailman$' N-Grams/3g 
2.879 seconds
time echo 'select * from trigram where A="the" and C="mailman";'|sqlite3 3g.db
15.839 seconds

Is there a way to speed sqlite3

up?

+3


source to share


1 answer


Create an index:

CREATE INDEX idx_trigram_col_c ON trigram(C);

and then try again.

You can get more speed on a second query with coverage index:



CREATE INDEX idx_trigram_col_c ON trigram(C, A);

or

CREATE INDEX idx_trigram_col_c ON trigram(A, C);

(if the columns are of different distributions, put the value with more values ​​first).

+6


source






More articles:


All Articles