We are seeing bad MySQL performance using the search feature. The search queries generated by homer 3.5 performed well on our db setup (two nodes), but the queries generated by homer 5 are leaving us a bit puzzled.
This is an example query (with sanitized data):
SELECT
t.id, date, (micro_ts DIV 1000) as milli_ts, micro_ts,method,reply_reason,ruri,ruri_user,
ruri_domain,from_user,from_domain,from_tag,to_user,to_domain,to_tag,pid_user,contact_user,
auth_user,callid,callid_aleg,via_1,via_1_branch,cseq,diversion,reason,content_type,auth,
user_agent,source_ip,source_port,destination_ip,destination_port,contact_ip,contact_port,
originator_ip,originator_port,correlation_id,proto,family,rtp_stat,type,node,'call' as trans,
'Example Node' as dbnode
FROM
sip_capture_call as t
WHERE
(t.date BETWEEN FROM_UNIXTIME(1454768710) AND FROM_UNIXTIME(1454768790))
AND
( ( from_user = '49123123456' OR from_user = '0049123123456' OR from_user = ' 49123123456' OR from_user = '0123123456' )
OR
( ruri_user = '49175123456' OR ruri_user = '0049175123456' OR ruri_user = ' 49175123456' ) OR ( pid_user = '49123123456' OR pid_user = '0049123123456' OR pid_user = ' 49123123456' )
)
order by t.id DESC LIMIT 100;
We have four sip_capture_call Tables on two Database nodes - two from our old homer 3.5 setup, two from the new homer 5 setup. All four of them can be successfully queried from homer 5. However, the search function produces a query like the one shown above. This is what EXPLAIN shows us:
Database A, Mysql 5.1, Homer 3.5 Table (query hangs forever in "sending data")
+----+-------------+-------+-------+-----------------------------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | t | range | ruri_user,from_user,pid_user,date | date | 4 | NULL | 11 | Using where; Using filesort |
+----+-------------+-------+-------+-----------------------------------+------+---------+------+------+-----------------------------+
Database A, MySQL 5.1, Homer 5 Table (good performance)
+----+-------------+-------+-------+-----------------------------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------+---------+---------+------+-------+-------------+
| 1 | SIMPLE | t | index | ruri_user,from_user,pid_user,date | PRIMARY | 12 | NULL | 17762 | Using where |
+----+-------------+-------+-------+-----------------------------------+---------+---------+------+-------+-------------+
Database B, MySQL 5.6, Homer 3.5 Table
+----+-------------+-------+-------+-----------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | ruri_user,from_user,pid_user,date | PRIMARY | 12 | NULL | 2 | Using where |
+----+-------------+-------+-------+-----------------------------------+---------+---------+------+------+-------------+
Database B, MySQL 5.6, Homer 5 Table
+----+-------------+-------+-------------+-----------------------------------+------------------------------+-------------+------+------+-----------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+-----------------------------------+------------------------------+-------------+------+------+-----------------------------------------------------------------------------+
| 1 | SIMPLE | t | index_merge | ruri_user,from_user,pid_user,date | from_user,ruri_user,pid_user | 302,302,302 | NULL | 10 | Using sort_union(from_user,ruri_user,pid_user); Using where; Using filesort |
+----+-------------+-------+-------------+-----------------------------------+------------------------------+-------------+------+------+-----------------------------------------------------------------------------+
At first we thought there might be a subtle difference in the homer 3.5 vs homer 5 sip_capture_calls table - but as MySQL 5.1 performs bad on the Homer 3.5 table and MySQL 5.6 performs bad on the homer 5 table, this seems not to be the issue here. Have you seen this behavior before?