CODEWITHSUNDEEP
mysqlsqlperformancejoinoptimization
user1238052
user1238052

Reputation: 43

MySQL: Optimize Join

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

Upvotes: 2

Views: 83

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

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

Upvotes: 3

Related Questions