[Solved] Join / subquery confusion in MySQL query

EverSQL Database Performance Knowledge Base

Join / subquery confusion in MySQL query

Database type:

I've re-written my query until I can't take it any more, so I was wondering if there is a better solution?

I have 5 tables;

The peopleOwners is a list of the ID's of the people and users (so more than one person can own a contact).

The contact table holds who and when a person was contacted (as well as the contact note obviously).

I'm trying to get a list of agents with the days since they last contacted each of their owned contacts (also those they haven't contacted yet).

My issue is that I need to get:

I am using a subquery but it's taking around 40 seconds so isn't really viable - I've checked and indexes look ok, the query is using 2 file scans which obviously isn't good!

SELECT po.FK_DivisionID, division_Name, po.FK_UserID_Owner, u.name, 
       p.PK_PeopleID, CONCAT_WS(' ', ppl_Title, ppl_FirstName,
       ppl_LastName) AS fullName, cc.maxID, cc.lastContact

  FROM People AS p, Users AS u, Divisions AS d, PeopleOwners AS po

  LEFT JOIN (
            SELECT contact_inputbyID, FK_PeopleID, MAX(c.contact_created) AS maxID,
                   DATEDIFF(NOW(), MAX(c.contact_created)) AS lastContact
            FROM Contact AS c
            GROUP BY FK_PeopleID
            ) AS cc
            ON cc.contact_inputbyID = po.FK_UserID_Owner
            AND cc.FK_PeopleID=po.FK_PeopleID

 WHERE po.FK_PeopleID = p.PK_PeopleID
   AND po.peopleowner_removed IS NULL
   AND po.FK_DivisionID=d.PK_DivisionID
   AND po.FK_UserID_Owner=u.PK_UserID

If anyone can give me some pointers in how to optimise it, it would be very much appreciated.

Thanks!

SqlFiddle of what I mean: http://sqlfiddle.com/#!2/ff7e62/2 (the fiddle works but in the live system the query is slow due to temporary table / file scans I think - is there a more optimised version possible?)

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 Subqueries (query line: 20): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
The optimized query:
SELECT
        po.FK_DivisionID,
        division_Name,
        po.FK_UserID_Owner,
        u.name,
        p.PK_PeopleID,
        CONCAT_WS(' ',
        ppl_Title,
        ppl_FirstName,
        ppl_LastName) AS fullName,
        cc.maxID,
        cc.lastContact 
    FROM
        People AS p,
        Users AS u,
        Divisions AS d,
        PeopleOwners AS po 
    LEFT JOIN
        (
            SELECT
                c.contact_inputbyID,
                c.FK_PeopleID,
                MAX(c.contact_created) AS maxID,
                DATEDIFF(NOW(),
                MAX(c.contact_created)) AS lastContact 
            FROM
                Contact AS c 
            GROUP BY
                c.FK_PeopleID 
            ORDER BY
                NULL
        ) AS cc 
            ON cc.contact_inputbyID = po.FK_UserID_Owner 
            AND cc.FK_PeopleID = po.FK_PeopleID 
    WHERE
        po.FK_PeopleID = p.PK_PeopleID 
        AND po.peopleowner_removed IS NULL 
        AND po.FK_DivisionID = d.PK_DivisionID 
        AND po.FK_UserID_Owner = u.PK_UserID

Related Articles



* original question posted on StackOverflow here.