What indexes to use for GroupWise max

Question

I have a user table and group table, and a many to many relationship between them.

I have a vote table (one to many relation between group and vote) And a subitem table (one to many between vote and subitem)

And I have a sub_item_actions table which is supposed to record which user performed what kind of action on which subitem. It has columns:

userid  
grp_id  
subitemid  
vote_id  
action type (enum)

For action type, it has the following values: delete update cast uncast

User can cast and uncast a subitem. For a user to 'belong' to a subitem , the latest cast or uncast action that the user has performed on a subitem should be of type cast. This means user has last cast on a subitem and has not uncast it yet.

I want to get all users that 'belong' to a sub item.

This is the query I am using :

select 
    act.*
from (
       select sub_item_id, user_id, max(created_at) as newestAction
       from vote_sub_item_action use  
       where sub_item_id = 10 
         AND action_type IN ('ACTION_CAST_VOTE', 'action_uncast_vote') 
       GROUP BY user_id
     ) as x 
   inner join 
     vote_sub_item_action as act 
   on  act.user_id = x.user_id 
   and act.created_at = newestAction 
   and act.action_type = 'action_cast_vote' 
   and act.sub_item_id = x.sub_item_id;

How should I index this table ?

I am using mysql (version 5.7)

Answer 1

Based on the information you have provided, here is my suggestion on how to get good performance when querying the latest users that have cast a vote on a given sub item (performance is assumed to be your goal, given that you are asking about what indexes to add.)

Create the following table:

CREATE TABLE vote_sub_item_cast_uncast (
vote_sub_item_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL,
vote_sub_item_action_id INT UNSIGNED NOT NULL,
PRIMARY KEY (`vote_sub_item_id`,`user_id`,`created_at`),
CONSTRAINT `VoteSubItemAction_VoteSubItemCastUncast_fk`
    FOREIGN KEY (`vote_sub_item_action_id`)
    REFERENCES `vote_sub_item_action` (`id`),
CONSTRAINT `VoteSubItem_VoteSubItemCastUncast_fk`
    FOREIGN KEY (`vote_sub_item_id`)
    REFERENCES `vote_sub_item` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The foreign keys are not required, but are there largely to help describe what columns point to which parent tables.

Whenever you insert into the vote_sub_item_action table, you must also insert into this new vote_sub_item_cast_uncast table, IF and ONLY IF, the action type is action_uncast_vote or action_cast_vote.

Then, you will be able to run the following query:

SELECT
vsicu.*
FROM vote_sub_item_cast_uncast vsicu
LEFT OUTER JOIN vote_sub_item_cast_uncast later_vsicu
ON later_vsicu.vote_sub_item_id = vsicu.vote_sub_item_id
AND later_vsicu.user_id = vsicu.user_id
AND later_vsicu.created_at > vsicu.created_at
INNER JOIN vote_sub_item_action vsia
ON vsia.id = vsicu.vote_sub_item_action_id
WHERE vsicu.vote_sub_item_id IS NULL
AND vsia.action_type = 'action_cast_vote'
AND vsicu.vote_sub_item_id = 10

The strategy employed here is the (second) one describe in the manual, for finding the groupwise maximum of a given column.

The primary key of the new table will be all the index you need. From your example, you are supplying the vote_sub_item_id as a constant, which is why that column is listed first. Then each user_id can have as many entries as there are unique created_at values (I think you have sub-second resolution in 5.7, but if not, that means at most once cast/uncast per second - be aware of this, and that your current query has an issue when querying such cases.)

The LEFT OUTER JOIN vote_sub_item_cast_uncast allows us to do an anti-join, and will return at most one row for each distinct user_id for the given vote_sub_item_id. From there, the INNER JOIN vote_sub_item_action will bring us back to the that table, allowing you to select the columns you need.

Why not just an index

I don't believe an index would be able to help, because of your IN clause, which functions as an OR and generally destroys the ability to use an index effectively. Your query itself is valid, and but I do not believe it would be fast for large amounts of data, due to that IN statement.