[Solved] MySQL slow query
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

MySQL slow query

Database type:

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Using Date Functions In Conditions (query line: 35): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. An alternative way is to use a range condition instead of a function call.
  2. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `recording_log`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
The optimized query:
SELECT
        CONCAT(DATE(vlog.call_date),
        '
',
        TIME(vlog.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(rlog.end_time,
        start_time) AS 'Length',
        location 
    FROM
        vicidial_log AS vlog 
    INNER 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
        vlog.call_date <= '2016-03-11 23:59:59' 
        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

Related Articles



* original question posted on StackOverflow here.