mysql - Get user's highest score from a table -

i have feeling simple question maybe i'm having brain fart right , can't seem figure out how go it.

i have mysql table structure below

+---------------------------------------------------+ | id |     date     |  score  |  speed  |  user_id  | +---------------------------------------------------+ |  1 |  2016-11-17  |  2      | 133291  |     17    | |  2 |  2016-11-17  |  6      | 82247   |     17    | |  3 |  2016-11-17  |  6      | 21852   |     17    | |  4 |  2016-11-17  |  1      | 109338  |     17    | |  5 |  2016-11-17  |  7      | 64762   |     61    | |  6 |  2016-11-17  |  8      | 49434   |     61    | 

now can particular user's best performance doing

select *  performance  user_id = 17 , date = '2016-11-17' order score desc,speed asc limit 1 

this should return row id = 3. want single query run able return 1 such row each unique user_id in table. resulting result this

+---------------------------------------------------+ | id |     date     |  score  |  speed  |  user_id  | +---------------------------------------------------+ |  3 |  2016-11-17  |  6      | 21852   |     17    | |  6 |  2016-11-17  |  8      | 49434   |     61    | 

also further more, can have question within same query further sort eventual resulting table same criteria of sort (score desc, speed asc). thanks

a simple method uses correlated subquery:

select p.*  performance p p.date = '2016-11-17' ,       p.id = (select p2.id               performance p2               p2.user_id = p.user_id , p2.date = p.date               order score desc, speed asc               limit 1              ); 

this should able take advantage of index on performance(date, user_id, score, speed).