Notice removed Draw attention by Paul White♦

occurred Apr 14, 2016 at 9:14

Bounty Ended with Willem Renzema's answer chosen by Paul White♦

occurred Apr 14, 2016 at 9:14

Tweeted twitter.com/StackDBAs/status/720175743913824256

occurred Apr 13, 2016 at 9:04

Notice added Draw attention by Paul White♦

occurred Apr 13, 2016 at 7:11

Bounty Started worth 100 reputation by Paul White♦

occurred Apr 13, 2016 at 7:11

added 181 characters in body

Source Link

edited Apr 10, 2016 at 21:20

ypercubeᵀᴹ

98.7k
13
215
305

I have a useruser table and groupgroup table, and a many to many relationship between them.

I have a votevote table (one to many relation between groupgroup and votevote) And a subitemsubitem table (one to many between votevote and subitemsubitem)

And I have a subitem actionssub_item_actions table which is supposed to record which useruser performed what kind of actionaction on which subitemsubitem. It has userid | grp_id | subitemid | vote_id | action type (enum)columns:

userid  
grp_id  
subitemid  
vote_id  
action type (enum)

For action type ,it 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

select 
    act.*
from (
       select sub_item_id,user_id user_id, max(created_at) as newestAction
       from vote_sub_item_action use  
       where sub_item_id =10= 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'action_type = 'action_cast_vote' 
   and act.sub_item_id =x= x.sub_item_id;

How should I index this table ?

I am using mysql (version 5.7)

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 subitem actions table which is supposed to record which user performed what kind of action on which subitem. It has 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)

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)

deleted 45 characters in body

Source Link

edited Apr 10, 2016 at 18:44

harveyslash

305
1
10

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 subitem actions table which is supposed to record which user performed what kind of action on which subitem. It has 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 INDEX (vote_sub_item_action_created_at_index) 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)

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 subitem actions table which is supposed to record which user performed what kind of action on which subitem. It has 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 INDEX (vote_sub_item_action_created_at_index) 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)

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 subitem actions table which is supposed to record which user performed what kind of action on which subitem. It has 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)

Source Link

asked Apr 10, 2016 at 18:38

harveyslash

305
1
10

What indexes to use for GroupWise max

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 subitem actions table which is supposed to record which user performed what kind of action on which subitem. It has 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 INDEX (vote_sub_item_action_created_at_index) 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)

Stack Exchange Network

Return to Question

What indexes to use for GroupWise max