Another way to write this query

Question

Whats another way to write this query?

My server takes a lot of time to execute this one:

SELECT c.id, c.franquicia_id, f.name, CONCAT(c.name,' ',c.surname) 
       contacto, c.created, DATE_FORMAT(c.created,'%d-%m-%Y') fecha 
FROM franquicias f, contacts c where f.id = c.franquicia_id 
 and c.created = (
                   select max(c2.created) 
                   from contacts c2 
                   WHERE c2.franquicia_id = c.franquicia_id
                 ) 
 and f.contract_id=2 
ORDER BY created DESC 
limit 10

I try to avoid correlated subqueries when possible. _...and 80's style joins._ — Uueerdo, Aug 10 '16 at 17:21
Check if contacts.created - indexed. Run "explain" on your query — Oleg Imanilov, Aug 10 '16 at 17:29
Generally, an uncorellated subquery will run faster than a correlated one. — Strawberry, Aug 10 '16 at 18:55

Uueerdo · Accepted Answer · 2016-08-10T17:39:19.607

This version of the subquery will only need run once for the entire query, rather than for each row of the outer query.

SELECT c.id, c.franquicia_id, f.name
   , CONCAT(c.name,' ',c.surname) AS contacto
   , c.created, DATE_FORMAT(c.created,'%d-%m-%Y') AS fecha 
FROM contacts AS c 
INNER JOIN franquicias AS f
   ON c.franquicia_id = f.id
    AND f.contract_id=2 
WHERE (c.franquicia_id, c.created) IN (
          SELECT franquicia_id, max(created) 
          FROM contacts
          GROUP BY franquicia_id
) 
ORDER BY created DESC 
LIMIT 10

Alternatively, the subquery can instead be used in an additional JOIN for filtering.

f.contract_id=2 can be put in either the ON or the WHERE; but (and the optimizer may eliminate any difference), keeping it in the ON could potentially reduce rows checked in the where, and makes it easier to later convert the query to a LEFT JOIN if it is ever needed to find "most recent contacts with no f's of contract_id 2".

Note: Indexing (franquicia_id, created) should improve the subquery, and the IN used against it; it should also improve the existing JOIN condition on franquicia_id.

score 0 · Answer 2 · edited May 23 '17 at 10:28

Quoting from this Post

I personally think the INNER JOIN is better, because it is more readable. It shows better the relations between the table. You got those relations in the join, and you do the filtering in the WHERE clause. This separation makes the query more readable.

SELECT 
    c.id, 
    c.franquicia_id, 
    f.name, 
    CONCAT(c.name,' ',c.surname) contacto, 
    c.created, 
    DATE_FORMAT(c.created,'%d-%m-%Y') fecha 
FROM franquicias f
INNER JOIN contacts c ON f.id = c.franquicia_id 
INNER JOIN 
(
    SELECT 
    C2.franquicia_id,
    MAX(C2.created) max_created
    FROM contacts C2 
    GROUP BY C2.franquicia_id
) AS maxCreatedForFrID
ON maxCreatedForFrID.franquicia_id = c.franquicia_id AND maxCreatedForFrID.max_created = c.created
WHERE f.contract_id = 2
ORDER BY created DESC
LIMIT 10;

Note:

A composite index (franquicia_id,created) in contacts table might boost the performance.

ALTER TABLE `contacts` ADD INDEX `idx_contacts_franquicia_id_created` (
    `franquicia_id`,
    `created`
);

Hey, it says : #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'C2.franquicia_id ) AS maxCreatedForFrID ON maxCreatedForFrID.franquicia_id = c' at line 16 — Donnie Darko, Aug 10 '16 at 17:46

score 0 · Answer 3 · answered Aug 10 '16 at 19:07

If franquicias contains not very lot of records, then next query must be more effective:

SELECT c.id, c.franquicia_id, f.name, CONCAT(c.name,' ',c.surname) 
       contacto, c.created, DATE_FORMAT(c.created,'%d-%m-%Y') fecha 
FROM (
    SELECT DISTINCT MAX(c2.created) as c2_created, f2.*
    FROM contacts c2
    JOIN franquicias f2 ON f2.id = c2.franquicia_id
) f
JOIN contacts c ON f.id = c.franquicia_id
AND c.created = f.c2_created
WHERE f.contract_id=2 
ORDER BY created DESC 
limit 10

Another way to write this query

3 Answers3