[question] query to get top 5 active user in each group, MySQL

Language: Layout:

DROP TABLE IF EXISTS `my_table`;
CREATE TABLE `my_table` (
  `id` int(11) NOT NULL,
  `user` int(11) DEFAULT NULL,
  `location` int(11) DEFAULT NULL,
  `usage` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `my_table` ADD PRIMARY KEY (`id`);
ALTER TABLE `my_table` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

INSERT INTO my_table values(null,27,1,286000);
INSERT INTO my_table values(null,27,1,286000);
INSERT INTO my_table values(null,37,1,200000);
INSERT INTO my_table values(null,17,1,200000);
INSERT INTO my_table values(null,12,1,580000);
INSERT INTO my_table values(null,12,1,580000);
INSERT INTO my_table values(null,28,1,105000);
INSERT INTO my_table values(null,9,1,120000);
INSERT INTO my_table values(null,9,1,120000);
INSERT INTO my_table values(null,28,1,105000);
INSERT INTO my_table values(null,6,1,344000);
INSERT INTO my_table values(null,22,1,190000);
INSERT INTO my_table values(null,22,1,190000);
INSERT INTO my_table values(null,14,1,170000);
INSERT INTO my_table values(null,14,1,170000);
INSERT INTO my_table values(null,20,1,600000);
INSERT INTO my_table values(null,20,1,600000);
INSERT INTO my_table values(null,20,1,150000);
INSERT INTO my_table values(null,20,1,150000);
INSERT INTO my_table values(null,19,1,150000);
INSERT INTO my_table values(null,19,1,150000);
INSERT INTO my_table values(null,31,1,230000);
INSERT INTO my_table values(null,33,1,470000);
INSERT INTO my_table values(null,33,1,470000);
INSERT INTO my_table values(null,9,1,276000);
INSERT INTO my_table values(null,9,1,276000);
INSERT INTO my_table values(null,26,1,160000);
INSERT INTO my_table values(null,9,1,200000);
INSERT INTO my_table values(null,24,1,240000);
INSERT INTO my_table values(null,26,1,160000);
INSERT INTO my_table values(null,23,2,424000);
INSERT INTO my_table values(null,34,2,20000);
INSERT INTO my_table values(null,34,2,620000);
INSERT INTO my_table values(null,34,2,600000);
INSERT INTO my_table values(null,13,2,20000);
INSERT INTO my_table values(null,13,2,12000);
INSERT INTO my_table values(null,13,2,469500);
INSERT INTO my_table values(null,13,2,30000);
INSERT INTO my_table values(null,30,2,20908);
INSERT INTO my_table values(null,30,2,300000);
INSERT INTO my_table values(null,30,2,14635);
INSERT INTO my_table values(null,30,2,125448);
INSERT INTO my_table values(null,30,2,14635);
INSERT INTO my_table values(null,30,2,16726);
INSERT INTO my_table values(null,11,2,54989);
INSERT INTO my_table values(null,11,2,2172068);
INSERT INTO my_table values(null,11,2,62687);
INSERT INTO my_table values(null,11,2,1237256);
INSERT INTO my_table values(null,35,2,150000);
INSERT INTO my_table values(null,38,2,150000);
INSERT INTO my_table values(null,10,2,500000);
INSERT INTO my_table values(null,36,2,34254);
INSERT INTO my_table values(null,36,2,400000);
INSERT INTO my_table values(null,36,2,82211);
INSERT INTO my_table values(null,1,2,26000);
INSERT INTO my_table values(null,8,2,20000);
INSERT INTO my_table values(null,18,2,25000);
INSERT INTO my_table values(null,18,2,20000);
INSERT INTO my_table values(null,2,2,550000);
INSERT INTO my_table values(null,2,2,228990);
INSERT INTO my_table values(null,15,3,19523);
INSERT INTO my_table values(null,15,3,23428);
INSERT INTO my_table values(null,15,3,11714);
INSERT INTO my_table values(null,15,3,68335);
INSERT INTO my_table values(null,15,3,250000);
INSERT INTO my_table values(null,29,3,20000);
INSERT INTO my_table values(null,29,3,21000);
INSERT INTO my_table values(null,29,3,180000);
INSERT INTO my_table values(null,29,3,20197);
INSERT INTO my_table values(null,29,3,27771);
INSERT INTO my_table values(null,29,3,23227);
INSERT INTO my_table values(null,29,3,15148);
INSERT INTO my_table values(null,29,3,180000);
INSERT INTO my_table values(null,29,3,19347);
INSERT INTO my_table values(null,29,3,793253);
INSERT INTO my_table values(null,29,3,14510);
INSERT INTO my_table values(null,29,3,299890);
INSERT INTO my_table values(null,4,3,184000);
INSERT INTO my_table values(null,4,3,40523);
INSERT INTO my_table values(null,4,3,55857);
INSERT INTO my_table values(null,4,3,26285);
INSERT INTO my_table values(null,4,3,46000);
INSERT INTO my_table values(null,4,3,15335);
INSERT INTO my_table values(null,4,3,165000);
INSERT INTO my_table values(null,32,3,23257);
INSERT INTO my_table values(null,32,3,31979);
INSERT INTO my_table values(null,32,3,29072);
INSERT INTO my_table values(null,32,3,11628);
INSERT INTO my_table values(null,32,3,34886);
INSERT INTO my_table values(null,32,3,17443);
INSERT INTO my_table values(null,7,4,18139);
INSERT INTO my_table values(null,7,4,84348);
INSERT INTO my_table values(null,7,4,43534);
INSERT INTO my_table values(null,7,4,253951);
INSERT INTO my_table values(null,7,4,580461);
INSERT INTO my_table values(null,7,4,317439);
INSERT INTO my_table values(null,7,4,489764);
INSERT INTO my_table values(null,7,4,59860);
INSERT INTO my_table values(null,7,4,36732);
INSERT INTO my_table values(null,7,4,19106);
INSERT INTO my_table values(null,7,4,47765);
INSERT INTO my_table values(null,7,4,26270);
INSERT INTO my_table values(null,7,4,23882);
INSERT INTO my_table values(null,7,4,80245);
INSERT INTO my_table values(null,7,4,18150);
INSERT INTO my_table values(null,7,4,25793);
INSERT INTO my_table values(null,7,4,14337);
INSERT INTO my_table values(null,5,4,19721);
INSERT INTO my_table values(null,5,4,104130);
INSERT INTO my_table values(null,5,4,857896);
INSERT INTO my_table values(null,5,4,11833);
INSERT INTO my_table values(null,5,4,141996);
INSERT INTO my_table values(null,5,4,483203);
INSERT INTO my_table values(null,5,4,197221);
INSERT INTO my_table values(null,3,4,18551);
INSERT INTO my_table values(null,3,4,5745763);
INSERT INTO my_table values(null,3,4,500881);
INSERT INTO my_table values(null,3,4,834805);
INSERT INTO my_table values(null,16,4,17272);
INSERT INTO my_table values(null,16,4,285000);

/*this query doesn't do the job*/
/*I need top 2 two users in each location based on their usage*/
select `user`, `location`, `usage`
from 
(
   select `user`, `location`, `usage`,
      (@num:=if(@group = `location`, @num +1, if(@group := `location`, 1, 1))) row_number 
  from `my_table` t
  CROSS JOIN (select @num:=0, @group:=null) c
  order by `location`, `usage` desc, user
) as x 
where x.row_number <= 2;


absolute service time: 0,48 sec