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?
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.
Anyone have any thoughts?
Thanks,
Martin
