3 ms·
Okay it finally (30 seconds ago!) finished indexing. The reason it ran out of disk space is that I included 3 columns to index on (in this case: name, path, fi
by bshipp 8y ago
Okay it finally (30 seconds ago!) finished indexing.
The reason it ran out of disk space is that I included 3 columns to index on (in this case: name, path, filename) and it ballooned my 66GB db to 185GB!
However, every single query afterward was instantaneous. Literally milliseconds to pull 90K results from three full-text columns across 500 million rows. And the search words were anywhere in the column, not just the beginning. Incredible. I'm simply blown away.
All I did was this single command:
CREATE VIRTUAL TABLE fts USING fts5(hash UNINDEXED, title, path, filename);
and then wrote a normal INSERT statement to populate it like I would a regular table. It was so painless.
Just be aware that each column appears to drastically increase the size of the DB.
I'm so excited! I have so many other databases to try this out on!
EDIT: now I'm going to move it off the SSD to a mechanical drive and see if it still holds the same performance.
- bshipp 8y agoFinal Comment on this: after moving it to a slow mechanical drive the query speed dropped dramatically, as expected. What was almost instantaneous on the SSD took anywhere from 40 to 120 seconds on the slower drive. However, previously the dumb full table scan took anywhere from 120-240 seconds on the SSD and I never even bothered trying on the slow drive!
- mosselman 8y agoWow, thanks for posting your results. This all sounds very promising. I am thinking about gathering a few pieces of data from custom sensors around the house in order to determine what I can do to cut down on energy costs (well that is the excuse ;)) and instead of using kibana and the like I’d rather use a lightweight SQLite dB, your stats make me hopeful of using it for this. Also it is all just very interesting.