On Tue, 12 Mar 2013 21:20:11 -0700
David King <dk...@ketralnis.com> wrote:
> > > At first I was just doing something like this pseducode:
> > > update_counter(k1, k2, count=count+1, expires=now+count*1day)
> > > if rows_updated != 1: insert_counter(k1, k2, count=1, expires=now
> > > +1day)
> >
> > Might I suggest that instead of trying to store an ever-changing
> > value, you simply figure it out when it's needed ?
>
>
> The logic is, "keep a given (k1, k2) pair around for one day for each
> time it's been seen". I could calculate it when it's needed, but
> since it's a computed value, I couldn't then have an index on it.
I'm not sure about SQLite, but in principle the query optimizer can
often use the base table's index for a derived value. Consider
A join B on A.a = 1 + B.a
or
A join B on A.a < sqrt(B.a)
An index on B.a is useful to finding the values meeting the criterion.
But perhaps you've measured this. How much faster is the updating
process you're concerned with than the SELECT that would avoid it?
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
