Mysql query grouping by two columns for max date takes too long I have table that looks like this:

l_p  l_a     l_timestamp          l_n   l_act   l_name
123  321     2011-1-1T01:00:00Z   B     q       das
21   23      2012-1-1T01:00:00Z   C     q       sd
123  23      2013-1-1T01:00:00Z   D     q       sad
21   321     2008-1-1T01:00:00Z   D     q       sad
21   23      2007-1-1T01:00:00Z   E     q       sad


The timestamp field is a binary. All other fields are varbinary. I am not interested in the l_name field but it is relevant for the indexes.

What I want to get back is for each unique combination of l_p and l_a the row with the max timestamp. I also have some additional conditions: the timestamp should be smaller than 2013-1-1 and the l_act field should be q. The table I want to get would look something like this:

l_p  l_a   l_timestamp          l_n   l_act
123  321   2011-1-1T01:00:00Z   B     q
21   23    2012-1-1T01:00:00Z   C     q
21   321   2008-1-1T01:00:00Z   D     q


The table contains about 50million records. My current query is extremely slow. It takes hours and still I haven't gotten a result yet. This is the query:

SELECT a.l_p,a.l_a,a.l_timestamp,
case a.l_new
when 'B' then 2
when 'C' then 3
...
end as GN
FROM tmp a
INNER JOIN 
(SELECT l_a,l_p,MAX(l_timestamp) as max_date
FROM tmp
WHERE l_act = 'q' AND (l_n != 'I' and l_n != 'Z')
AND l_timestamp &lt; cast('2013-01-01T00:00:00Z' as binary)
GROUP BY l_p,l_a) b
on (a.l_p = b.l_p AND a.l_a = b.l_a AND
a.l_timestamp = b.max_date AND
a.l_act = 'q' AND  (l_n != 'I' and l_n != 'Z')


I've also tried this variation with the same results:

SELECT a.l_p,a.l_a,a.l_timestamp,
case a.l_new
when 'B' then 2
when 'C' then 3
...
end as GN
FROM tmp a
(SELECT l_a,l_p,MAX(l_timestamp) as max_date
FROM tmp
WHERE l_act = 'q' AND (l_n != 'I' and l_n != 'Z')
AND l_timestamp &lt; cast('2013-01-01T00:00:00Z' as binary)
GROUP BY l_p,l_a) b
WHERE (a.l_p = b.l_p AND a.l_a = b.l_a AND
a.l_timestamp = b.max_date AND
a.l_act = 'q' AND  (l_n != 'I' and l_n != 'Z'))


The indexes on the table are:

Keyname     Type         Unique    Packed   
PRIMARY     BTREE        Yes       No   
Field       Cardinality            Collation
l_project   18           A      
l_name      18           A  
l_a         24799255     A  
l_act       49598510     A  
l_timestamp 49598510     A

AND
Keyname     Type          Unique Packed 
l_a         BTREE         No     No 
Field       Cardinality          Collation
l_a         16532836      A     
l_name      16532836      A 


Any ideas on why it takes so long? The table is innodb.
EDIT 1: The query below is quite like the original query. It very much depends on how strict are the other conditions, i.e. how many rows can be ignored with those conditions. I think the index should contain the group-max-triple (l_a, l_b, timestamp), but if l_act='q' is a strong reduction and the condition is always there then l_act should come before l_a.

Origin:
I would write it as 

select tmp.* 
from tmp inner join (
    select l_a, l_b, max(timestamp) as max_time 
    from tmp where timestamp &lt; cast('2013-01-01T00:00:00Z' as binary) 
    group by l_a, l_b
) as selmax 
on selmax.l_a = tmp.l_a 
   and selmax.l_b = tmp.l_b 
   and selmax.max_time = tmp.timestamp
; 


and give tmp an index on (l_a, l_b, timestamp).

And also: what says explain?

EDIT 2: 

Seeing the explain and looking at the indices clears the case. l_project starts the primary key but you give no restriction on that in your inner query.

The second index needs name as a second field.

If l_act is a strong restriction (All of the queries give a l_act - condition which meets less than  1/3 of the table), you should make an index 

l_act, l_a, l_b, timestamp


otherwise

l_a, l_b, timestamp


If the 4-part-index is used, it should be very fast.

I wish much fun adding the index. ;-)

If you use the 4-part-index, then you must add the l_act-condition in the inner AND the outer join.