Slow queries with large number of rows

MySQL & MariaDB Other MySQL® Questions
1

I’m having a problem with some queries taking 1+ minutes or so to run.
Any suggestions on what I can do to improve performance?
This is results for a web app so a fast result set is needed.

Some of the tables have a lot of rows in them:

mysql> show table status \G
Name: call_ratings
Rows: 48629361
Name: cdr_calls
Rows: 123150376
Name: cdr_main
Rows: 199418748
Name: line_item_ratings
Rows: 2625774

I’m running a query like this:

SELECT * from
((SELECT
extract(YEAR from if(invoice_id > 0, period_start, rated_time)) as year,
extract(MONTH from if(invoice_id > 0, period_start, rated_time)) as month,
if(is_caller = 1, caller, destination) as tn,
caller, destination, 
call_class,
count(*) as call_count,
sum(bill_min) as total_minutes,
sum(duration) as total_seconds, 
sum(charge) as total_charge
FROM cdr_calls
LEFT JOIN call_ratings on call_id = cdr_calls.id 
LEFT JOIN call_invoices on invoice_id = call_invoices.id
WHERE cdr_calls.caller_user_id = '70956' and bill_min > 0
group by year, month, tn, call_class
order by year, month, tn, call_class
)
UNION 
(SELECT
extract(YEAR from start_time) as year,
extract(MONTH from end_time) as month,
if(is_caller = 0, destination, caller) as tn,
caller,destination,

call_class,
count(*) as call_count,
sum(null) as total_minutes,
sum(duration) as total_seconds, 
sum(null) as total_charge
FROM cdr_calls 
LEFT JOIN cdr_main ON a_leg_cdr_id = cdr_main.id 
LEFT JOIN call_ratings ON call_id = cdr_calls.id 
WHERE cdr_calls.dest_user_id = '70956' and duration > 0
group by year, month, tn, call_class
order by year, month, tn, call_class)) derived_t_alias
group by year, month, tn, call_class
order by year, month, tn, call_class


+----+--------------+---------------+--------+------------------------+----------------+---------+-----------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------+--------+------------------------+----------------+---------+-----------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 10349 | Using temporary; Using filesort |
| 2 | DERIVED | cdr_calls | ref | PRIMARY,caller_user_id | caller_user_id | 5 | const | 6183 | Using temporary; Using filesort |
| 2 | DERIVED | call_ratings | ref | call_id | call_id | 4 | cdr.cdr_calls.id | 1 | Using where |
| 2 | DERIVED | call_invoices | eq_ref | PRIMARY | PRIMARY | 4 | cdr.call_ratings.invoice_id | 1 | NULL |
| 3 | UNION | cdr_calls | ref | dest_user_id | dest_user_id | 5 | const | 4166 | Using where; Using temporary; Using filesort |
| 3 | UNION | cdr_main | eq_ref | PRIMARY | PRIMARY | 4 | cdr.cdr_calls.a_leg_cdr_id | 1 | Using index |
| 3 | UNION | call_ratings | ref | call_id | call_id | 4 | cdr.cdr_calls.id | 1 | NULL |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+---------------+--------+------------------------+----------------+---------+-----------------------------+-------+----------------------------------------------+

Thanks,
William

2

Your query is using indexes according to EXPLAIN’s output.

Any suggestions on what I can do to improve performance?

I have a few, try these:

MySQL manual says:
If a query includes GROUP BY but you want to avoid the overhead of sorting the result,
you can suppress sorting by specifying ORDER BY NULL.

So, Instead of:
order by year, month, tn, call_class

Try:
ORDER BY NULL, to get rid of “Using filesort” in EXPLAIN, and then perhaps you can sort the results later, in client-side?

At least, you can avoid the ORDER BYs in your sub-queries, and do it once after UNION.
( Note that you have to mention ORDER BY NULL explicitly, because GROUP BY performs a sort by itself )

Disk performance can become an issue when you have millions of rows.
Changing to a faster disk might not be an option, but still I’d suggest to OPTIMIZE your tables,
this improves the index lookup. ( Note that OPTIMIZE TABLE would lock your table for a few min to hours )

@see: http://dev.mysql.com/doc/refman/5.1/…ize-table.html

By the way, are you running this query on the master with a lot of write/read I/O, or on the read-only slave?

MyISAM is usually better than InnoDB to run Aggregation queries. Perhaps you can replicate your InnoDB
database to a read-only MyISAM to run these kind of queries.