MySql get row with max revision from 2 different column

Question

This is my Table:

id  dl_id   rev1    rev2  
1    48      1       0  
2    48      1       1  
3    50      0       2  
4    50      1       2  
5    50      2       1  

This is wanted Result:

id  dl_id  
2   48  
4   50

rev2 has more priority than rev1 , so I want id for dl_id with max revison.

this is my query :

select distinct dl_id,  
  (select id from myTable   
   where  dl_id=m.dl_id  
   order by rev2 desc,rev1 desc limit 0,1) as id  
from myTable m 

my query is too slow , its took 4 seconds to run. I need a better query

Answer 1

E.g (and note the indexes):

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dl_id INT NOT NULL
,rev1 INT NOT NULL 
,rev2 INT NOT NULL
,UNIQUE(dl_id,rev1,rev2)
);

INSERT INTO my_table VALUES
(1    ,48    ,1    ,0),
(2    ,48    ,1    ,1),
(3    ,50    ,0    ,2),
(4    ,50    ,1    ,2),
(5    ,50    ,2    ,1);

SELECT x.*
  FROM my_table x
  JOIN
     ( SELECT a.dl_id
            , a.rev2
            , MAX(a.rev1) rev1
         FROM my_table a
         JOIN 
            ( SELECT dl_id
                   , MAX(rev2) rev2 
                FROM my_table 
               GROUP 
                  BY dl_id
            ) b
           ON b.dl_id = a.dl_id 
          AND b.rev2 = a.rev2
        GROUP
           BY a.dl_id
            , a.rev2
     ) y
    ON y.dl_id = x.dl_id
   AND y.rev2 = x.rev2
   AND y.rev1 = x.rev1;

+----+-------+------+------+
| id | dl_id | rev1 | rev2 |
+----+-------+------+------+
|  2 |    48 |    1 |    1 |
|  4 |    50 |    1 |    2 |
+----+-------+------+------+