What indexes to use for GroupWise max
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 &gt; 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.