On 29/12/2014 4:33 PM, Baruch Burstein wrote:
Hi,I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a given value of 'a', the highest matching 'b'. The query itself it simple: SELECT max(b) FROM t WHERE a=:whatever To speed this up, I would add an index on 'a'. Now, the question is is there some way to tell the index that I am only interested in the maximum value of b? For example, for the following table: a|b 1|1 1|2 2|2 2|3 I only need the index to contain the rows (1,2) and (2,3). The docs for partial indexes say that they can't contain functions (like max()). Any suggestions?
I don't know if you can do a partial index for this, but if this is a use case that is slow for you and is very critical, I would use a trigger to update a separate table that stores just the value of "a" and its corresponding maximum. It's quite easy to manage this for the create and insert cases. You may have to run that whole query in the trigger again if there a deletion done. So, in a way this table is the cache you want.
Not sure if that helps your case. Best Regards, Mohit. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
