Announcement

Announcement Module
Collapse
No announcement yet.

Slow queries with large number of rows

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow queries with large number of rows

    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:
    Code:
    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:
    Code:
    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
    Code:
    +----+--------------+---------------+--------+------------------------+----------------+---------+-----------------------------+-------+----------------------------------------------+
    | 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.
    Last edited by amirbehzad; 02-17-2014, 01:28 AM.

    Comment

    Working...
    X