Efficiency of partial indexes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Efficiency of partial indexes

Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Efficiency of partial indexes

David Burgess-2
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users