0

I have a table that looks like this:

CREATE TABLE `message` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `user_id` int(10) unsigned NOT NULL,

  `grp_id` int(10) unsigned NOT NULL,

  `body` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`created_at`,`grp_id`,`user_id`,`id`),

  KEY `message_user_id_foreign` (`user_id`),

  KEY `message_grp_id_foreign` (`grp_id`),

  KEY `mssg_id_is` (`id`),

  CONSTRAINT `message_grp_id_foreign` FOREIGN KEY (`grp_id`) REFERENCES `groups` (`id`) ON UPDATE CASCADE,

  CONSTRAINT `message_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I want to fetch the latest 50 MESSAGES for every group that the user is in. This list will be supplied in the query . (something like 8,4,2,1,6,...)

What will the mysql query look like? The number of groups a user is in is variable.

8
  • I have no idea where to start with this. Commented Jan 19, 2016 at 13:32
  • 1
    And I have no idea what you mean by "This list will be supplied in the query . (something like 8,4,2,1,6,...)"
    – jera
    Commented Jan 19, 2016 at 13:34
  • its just a list of integers that will probably be supplied like where ____ IN(5,6,7,3,1,etc) Commented Jan 19, 2016 at 13:35
  • you could start with this select body from message where user_id = ? and group_id in (x,y,z) limit 50
    – jera
    Commented Jan 19, 2016 at 13:39
  • 2
    You are into the groupwise max class of problems. As you will see from that link, it is not straightforward to get what you want.
    – Rick James
    Commented Jan 20, 2016 at 2:24

1 Answer 1

2

This is best done by LATERAL JOINs or if it is not available window functions. MySQL unfortunately doesn't support either but the latter is relatively easy to be mimicked.

> set @rownum=0;
> set @grp_lag=null; 
> select id, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id  
from message 
order by grp_id, created_at desc;

+-------+--------+------------------+
| id    | rownum | @grp_lag:=grp_id |
+-------+--------+------------------+
| 50008 |      1 |                1 |
| 50007 |      2 |                1 |
| 50001 |      3 |                1 |
| 50004 |      4 |                1 |
| 50002 |      1 |                2 |
| 50005 |      2 |                2 |
| 50003 |      1 |                3 |
| 50006 |      1 |                6 |
+-------+--------+------------------+
8 rows in set (0.00 sec)

After having this you can have the final query something like this:

-- Set initial values for mimicing the window function
set @rownum=0;
set @grp_lag=null; 

-- Query every message where rownum < per group limit
select * from (
    -- subquery providing rownumber for every group order by the created_at column
    select *, case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end as rownum, @grp_lag:=grp_id 
    from message 
    where grp_id in (1,2)
    -- Futher filtering for optimisation if possible
    order by grp_id, created_at desc
) numbered
where rownum < 50;

You can wrap this into a procedure to execute it easier. Please keep in mind, set calls also return and they need to be in the same session (yet another reason for a stored procedure).

> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)

The query above will fetch every row where grp_id matches the in condition and generate the row number for each per group. So in case of large number of messages further optimisations are necessary. For example you can heuristically filter by created_at > [estimated date for having at least 50 message].

1
  • You could probably reduce case when @grp_lag != grp_id then @rownum:=1 else @rownum:=@rownum+1 end to @rownum := @rownum * (@grp_lag = grp_id) + 1.
    – Andriy M
    Commented Jan 20, 2016 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.