MySQL: Optimize Join

Question

I currently try to optimize a MySQL statement. It takes about 10 sec and outputs an average difference of two integer. The event table contains 6 cols and is indexed by it's id and also by run_id + every other key.

The Table holds 3308000 rows for run_id 37, 4162050 in total. Most time seems to be needed for the join, so maybe there is a way to speed it up.

send.element_id and recv.element_id are unique, is there a way to express it in sql which might lead in a better performance?

|-------------------
|Spalte      Typ 
|-------------------
|run_id      int(11)
|element_id  int(11)
|event_id    int(11)      PRIMARY
|event_time  int(11)
|event_type  varchar(20)
|event_data  varchar(20)

The Query:

select avg(recv.event_time-send.event_time)
from 
(
 select element_id, event_time
 from event
 where run_id = 37 and event_type='SEND_FLIT'
) send,
(
 select element_id, event_time
 from event
 where run_id = 37 and event_type='RECV_FLIT'
) recv
where recv.element_id = send.element_id

The Explain of the Query:

+----+-------------+------------+------+-----------------------------------------------------+-------------+---------+-------------+--------+-----------------------+
| id | select_type | table      | type | possible_keys                                       | key         | key_len | ref         | rows   | extra                 |
+----+-------------+------------+------+-----------------------------------------------------+-------------+---------+-------------+--------+-----------------------+
|  1 | PRIMARY     | <derived3> | ALL  | NULL                                                | NULL        | NULL    | NULL        | 499458 | NULL                  |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>                                         | <auto_key0> | 4       | element_id  | 10     | NULL                  |
|  3 | DERIVED     | event      | ref  | run_id,run_id_2,run_id_3,run_id_4,run_id_5,run_id_6 | run_id_5    | 26      | const,const | 499458 | Using index condition |
|  2 | DERIVED     | event      | ref  | run_id,run_id_2,run_id_3,run_id_4,run_id_5,run_id_6 | run_id_5    | 26      | const,const | 562556 | Using index condition |
+----+-------------+------------+------+-----------------------------------------------------+-------------+---------+-------------+--------+-----------------------+

If "send.element_id and recv.element_id are unique" then the 'join' "recv.element_id = send.element_id" means you are on the same row? No? — Mitch Wheat
– Mitch Wheat, Commented Mar 2, 2016 at 23:36
I mean that there is only one 'SEND_FLIT' event per element_id, thus the column is unique. Same applies to 'RECV_FLIT'. So I'm not on the same row, but it only exists one other row that can be joined. — user1238052
– user1238052, Commented Mar 2, 2016 at 23:45

FuzzyTree · Accepted Answer · 2016-03-03 00:04:27Z

3

One way is to group by element_id and to use sum to determine the difference, which you can then pass to avg.

select avg(diff) from (
    select 
        sum(case when event_type = 'SEND_FLIT' then -1 * event_time else event_time end)
        as diff
    from event
    where run_id = 37
    and event_type in ('SEND_FLIT','RECV_FLIT')
    group by element_id
) t

answered Mar 3, 2016 at 0:04

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1238052 Over a year ago

I really like this approach, but performance wise it's in the same league (just tested it, was slight above 10s). The group by searches for all values of element_id and won't stop if it found 2 (my guess). I don't know if it's possible to use the knowledge about the data records inside a sql statement.

FuzzyTree Over a year ago

this query assumes that there is only 1 RECV_FLIT and SEND_FLIT per element_id, which means each group will have 2 elements. a composite index on (run_id,event_type,element_id) might help.

user1238052 Over a year ago

The additional index seems to help a little bit, guess i was wrong about the group, forgot about the indexes at all. The query seems to be as good as possible, so I'll live with that 8s left.

Collectives™ on Stack Overflow

MySQL: Optimize Join

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related