Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 < 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 < 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.

share|improve this question
    
What about a siple group by? select l_a, l_b, max(timestamp) as max_time from tmp where timestamp < cast('2013-01-01T00:00:00Z' as binary) group by l_a, l_b I can not yet understand what the subselects are for. –  flaschenpost May 25 '13 at 9:57
    
Yes that would work but I am also obtaining a.l_n in my select process. A simple group and max doesn't work properly because it just picks any random row from l_n while I want the one that corresponds to the max(timestamp) per pair l_a,l_p –  Michael Tsikerdekis May 25 '13 at 10:35
    
What does EXPLAIN show? Paste that into your question. –  Mike Sherrill 'Cat Recall' May 25 '13 at 11:22
    
Would be useful to know but since the query has not completed for what it is 12 hours by now, I won't know what explain has to say. –  Michael Tsikerdekis May 25 '13 at 11:38
    
The isolated explain of the inner subquery may be easier obtained, because explain may take a long time for the complete query. –  flaschenpost May 25 '13 at 11:44
show 1 more comment

migrated from stackoverflow.com May 28 '13 at 1:13

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

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 < 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.

share|improve this answer
    
I wrote the same query. Keep in mind that l_act and l_n might need to be indexed, too. –  Mike Sherrill 'Cat Recall' May 25 '13 at 11:45
    
Yeah, you are right, it's just the same (as I saw too late). Then only the explain could give a hint, especially the inner explain. It could be necessary to insert a use index. –  flaschenpost May 25 '13 at 12:37
    
I started preparing the index and its been hours and still its been built. I tried this query however and it actually is processed up to a point fast but now it gets stuck at "sending data" status. I set "INTO OUTPUT" so that i can see what is happening and apparently data are sent to the file when I do tail on linux but they are exported extremely slowly. It takes a couple of minutes for 100 rows and it happens in increments (so 100 rows are pasted and then wait 100 secs and the again 100 rows...). Note: this is still without the indexes which are currently being built. –  Michael Tsikerdekis May 25 '13 at 20:27
    
firing the query and building the index are two operations on the same table. Every query will be waiting for the index-operation, and the index waits for a running query. I would advise to stop the original select, build the index and then fire the query. // Hint: For analytical tests you could use a smaller version of the table, say create table tmp_test like tmp; insert into tmp_test select * from tmp where rand() < 0.001; - then checking and timing different versions is a bit more fun. –  flaschenpost May 26 '13 at 10:20
    
What if I were to create a new tmp table, create there all the indexes and the structure of the original, and then do an insert select? Would that be faster instead of rebuilding indexes? –  Michael Tsikerdekis May 26 '13 at 18:11
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.