MySQL slow query

MySQLperformance

This is my first time posting here. I am new to MySQL and trying to learn it as fast as possible but this issue is causing a problem to production and need to reach out for help.

This server is running Suse linux and has a MariaDB 5.5.46. We have been having a huge spike in threads on this active server and I have been digging into this for a little while. I have used Jet Profiler to get a more visual idea of what is going on while looking into this matter and a few days ago I saw this spike live on the server shooting up to 600 threads for about 2 minutes. Thanks to that i have isolated a query that even in the CLI is taking 45 to 57 sec to complete.

This is the offending query that i have pulled out of the Jet Profiler. it is coming from a web site pulling a list off the source for an external person to view the contents. The active server that it is pulling from is phone system. 

SELECT CONCAT( DATE(call_date),'<br/>', TIME(call_date) ) as DateTime
    , vlog.campaign_id AS Campaign
    , CONCAT(emp.full_name,' [',vlog.user,']') AS 'Rep'
    , CONCAT(lead.first_name,' ',lead.last_name) as 'Name'
    , CONCAT(lead.state,'/',lead.city) AS 'State-City'  , lead.lead_id
    , vlog.status as CallSts
    , lead.status as LeadSts
    , vlog.phone_number AS 'Phone'
    , timediff(end_time,start_time) as 'Length'
    , location
FROM
    vicidial_log as vlog 
    LEFT JOIN recording_log as rlog  ON vlog.uniqueid = rlog.vicidial_id
    LEFT JOIN vicidial_list as lead  ON vlog.lead_id = lead.lead_id 
    LEFT JOIN vicidial_users as emp  ON emp.user      = rlog.user
WHERE
    date(vlog.call_date) <= '2016-03-11'
    AND vlog.status <> 'INCALL'
    AND vlog.length_in_sec > 10
    AND vlog.campaign_id like 'AG%' 
    AND location IS NOT NULL
   AND rlog.end_time IS NOT NULL

Here is the EXPLAIN addition. 

+----+-------------+-------+--------+----------------------+-------------+---------+---------------------------+---------+---------------------------------------------------------------------+
| ID | select_type | table |  type  |    possible_keys     |     key     | key_len |            ref            |  rows   |                               Extra                                 |
+----+-------------+-------+--------+----------------------+-------------+---------+---------------------------+---------+---------------------------------------------------------------------+
|  1 | SIMPLE      | rlog  | range  | filename,vicidial_id | vicidial_id |      63 | NULL                      | 1040870 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | vlog  | eq_ref | PRIMARY              | PRIMARY     |      62 | asterisk.rlog.vicidial_id |       1 | Using where                                                         |
|  1 | SIMPLE      | lead  | eq_ref | PRIMARY              | PRIMARY     |       4 | asterisk.vlog.lead_id     |       1 |                                                                     |
|  1 | SIMPLE      | emp   | eq_ref | user                 | user        |      62 | asterisk.rlog.user        |       1 | Using where                                                         |
+----+-------------+-------+--------+----------------------+-------------+---------+---------------------------+---------+---------------------------------------------------------------------+

Best Answer

It looks like indexes are not properly used.

You have 6 WHERE (AND) clauses, each of them should be analyzed from an index selection perspective :

clause 1: DATE(vlog.call_date) <= '2016-03-11'

This one won't be a good candidate because:

  1. you cast your call_date with a DATE() function, making any index on this column totally unusable.
  2. a call_date lower than a recent date would not filter a lot of records anyway since most of the calls would likely be in the range

clause 2: vlog.status <> 'INCALL'

A not equal clause won't be able to use any status based index as far as I can tell.

clause 3: vlog.length_in_sec > 10

I can imagine most of the calls would last more than 10s, so an numerical index based on this column would probably not filter many records, if ever defined. May depend on actual recorded data though.

clause 4: vlog.campaign_id like 'AG%'

I like this one. A string based index would filter on the compaigns stating with these two characters. If text were equally distributed upon alphabetical letters — LIKE clause being case insensitive — you would have a 26x26=612 factor filter. It's less probable, but I would at least say a 100 factor, which is kind of a good start.

You'd still have to add the index on that column if not existing, and also make sure you would launch the query with at least 2 letters, to be efficient.

clause 5: location IS NOT NULL

Nullable columns can be indexed and filtered on null values, but they are arguably very common values that would not filter a lot of records. I would preferably follow the clause 4 lead.

clause 6: rlog.end_time IS NOT NULL

Same as clause 5.

To summarize, I would add an index on compaign_id and see what happens.

Hope that helps.

Related Question