--- Martin O'Leary <[EMAIL PROTECTED]> wrote:
> Hi guys, > > I have a table like the following: > > CREATE TABLE user_actions ( > uid INTEGER NOT NULL, > actionid INTEGER NOT NULL, > time INTEGER NOT NULL, > status INTEGER NOT NULL, > PRIMARY KEY (uid, actionid, time, status) > ); > > And I want to carry out a query something like this: > > SELECT uid, actionid, MAX(time), status FROM user_actions GROUP BY > uid, actionid; > > i.e. finding the last time each user performed each action (numbers of > users and distinct actions are small in comparison to number of times > each user performs each action). > > Here, I find two problems. Firstly, because my table doesn't have an > INTEGER PRIMARY KEY, I get an autogenerated index on my compound key. > No problem there. However, and I may be missing something, it seems > that there's a lot of data duplication going on. All the data from my > main table is available in the index (status shouldn't really be part > of the key, but I added it in order to increase query performance). As > far as I can tell, this means that the main table is never consulted, > but just sits there, doubling the size of my database. Is there any > way around this? You can't do anything about that unfortunately. > Secondly, query performance is quite slow. It seems to me that no > optimisation is being carried out on the MAX(time) expression. Is this > the case, and if so, why not? Surely it's possible to do this in a > nice, logarithmic way. New versions of SQLite should execute this query in O(N) time. Are you seeing otherwise? I don't really see how you could improve on that given the query and index. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
