On the systems I use, for log files, you cant beat a text file for integrity and speed.
I would suffer the slower queries and use something like the CSV extension on your text log files. On Mon, Jul 30, 2018 at 10:44 AM, Simon Slavin <[email protected]> wrote: > I have a particular logging task which is time-critical, both in reading and > writing. It runs on Linux, but not a fast desktop computer, more like the > slow kind built into your WiFi router, with limited solid state storage. I > can use any version of the SQLite C API I want, and currently use a minimal > build which omits almost all optional parts. Remember: on this setup > processing is slow, storage space limited. > > Log entries are written from six a minute (peak time) to one an hour (night). > In some use-cases queries will be frequent (a few a day). In others, users > are not interested and weeks may pass without a query. It has the age-old > problem when you need random access to the data: > > A) Create no indexes. Fast writing, but slow when looking things up. > B) Create indexes. Slower writing, but faster when looking things up. > > Naturally, I want it all. I'd been toying with the idea that initial writing > should be to a text file, and data flushed to SQLite just before a query is > executed. But a recent SQLite innovation changes things. Instead of using a > text file I can use partial indexes. > > So I add a column to my table called "searchable". It starts off set to > FALSE. I replace my indexes with partial indexes which count only > "searchable" rows. When a search is done, before actually doing the search I > do > > UPDATE MyTable SET searchable = TRUE <WHERE searchabble = FALSE ?> > > This, theoretically, updates the indexes. Does anyone have experience with > this ? The programming is simpler if I use this trick, since I don't have to > handle and flush a text file. But I haven't used partial indexes before. > Any advice or suggestions before I do testing ? Is there a better way I've > missed entirely ? > > Simon. > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
