\r\ncreate table user(\r\n  id int not null auto_INCREMENT, \r\n  name varchar(10), \r\n  PRIMARY KEY (id)\r\n)ENGINE=innodb;\r\n\r\ncreate table post(\r\n  id int not null auto_INCREMENT,\r\n  user_id int,\r\n  topic varchar(10),\r\n  score int,\r\n  PRIMARY KEY (id),\r\n  FOREIGN KEY (user_id) REFERENCES user(id)\r\n)ENGINE=InnoDB;\r\n\r\nCREATE INDEX user_score_idx ON post (user_id,score );\r\n

\r\nselect u.id,name,topic,score from (\r\n     select p1.user_id, max(p1.score) max_score \r\n       from post p1\r\n      group by p1.user_id ) zz\r\n  join post p on zz.max_score=p.score and zz.user_id = p.user_id\r\n  join user u on u.id=p.user_id\r\n

\r\n   select u.id, u.name, p0.topic, p0.score \r\n    from user u join post p0\r\n      on p0.id = (select max(id) from post p1 \r\n                   where p1.user_id=u.id\r\n                     and p1.score = (select max(p2.score) \r\n                                       from post p2 \r\n                                      where p2.user_id=p1.user_id))\r\n

\r\n    select u.id, u.name, p0.topic, p0.score \r\n      from user u join post p0\r\n        on p0.id = ( select p1.id \r\n                       from post p1 \r\n                      where p1.user_id=u.id \r\n                      order by -p1.score, -p1.id \r\n                      limit 1)\r\n

\r\n   select u.id, u.name, p1.topic, p1.score \r\n    from \r\n       user u left join post p1 \r\n                     on u.id = p1.user_id\r\n              left join post p2 \r\n                     on p1.user_id=p2.user_id and p1.score < p2.score \r\n    where p2.id is null\r\n

 select u.id,name,topic,score from (
     select max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  join post p on zz.max_score=p.score
  join user u on u.id=p.user_id

select u.id,name,topic,score from (
     select p1.user_id, max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  join post p on zz.max_score=p.score and zz.user_id = p.user_id
  join user u on u.id=p.user_id

select u.id,name,topic,score from (
     select p1.user_id, max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  join post p on zz.max_score=p.score and zz.user_id = p.user_id
  join user u on u.id=p.user_id

select u.id,name,topic,score from (
     select p1.user_id, max(p1.score) max_score 
       from post p1
      group by p1.user_id ) zz
  STRAIGHT_JOIN post p on zz.max_score=p.score and zz.user_id = p.user_id
  STRAIGHT_JOIN user u on u.id=zz.user_id