Mysql GROUP BY and ORDER BY DESC

Question

This is my full query:

SELECT * FROM `clientgroupassign`
LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId
LEFT JOIN `users` ON `carerId` = `userId`
LEFT JOIN 
    (SELECT * FROM 
        (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC) 
        as `contacts` 
    GROUP BY (`contactClientId`)
    )  AS `contacts` ON `contactClientId` = `clients`.clientId
WHERE groupId = 4
ORDER BY `clients`.clientId

There is a problem with third join causing the script to execute for about 1 minute. When I run it separattly in PMA:

SELECT * FROM (SELECT * FROM `contacts` WHERE `contactGroup` = 4 ORDER BY `contactId` DESC) AS `contacts` GROUP BY (`contactClientId`)

it still gets very long to execute.

What I want is to get one, last added row from contacts for each client who is in the group 4(client can be in various groups).

Thanks.

Also, remove `SELECT * ...` from there. Select specific columns. It might add overhead. `SELECT *` might be a real speed killer. — BlitZ, Sep 16 '13 at 15:04

Stephan · Answer 1 · 2013-09-17T06:58:14.557

To get "last added row from contacts for each client who is in the group 4" try this:

SELECT
    c.*
FROM(
    SELECT
        contactClientId,
        MAX(contactId) as cid
    FROM
        contacts
    WHERE
        contactGroup = 4
    GROUP BY
        contactClientId
    ORDER BY
        NULL
) as tmp
INNER JOIN contacts as c
    ON c.contactId = tmp.cid
    AND c.contactClientId = tmp.contactClientId

If contactId is PK in contacts then the second join clause is not needed.

By default, MySQL sorts all GROUP BY col1, col2, ... queries as if you specified ORDER BY col1, col2, ... in the query as well. If you include an ORDER BY clause explicitly that contains the same column list, MySQL optimizes it away without any speed penalty, although the sorting still occurs. 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.

The complete docs.

Full query:

SELECT * FROM `clientgroupassign`
LEFT JOIN `clients` ON `clientgroupassign`.clientId = `clients`.clientId
LEFT JOIN `users` ON `carerId` = `userId`
LEFT JOIN 
    (
        SELECT
            c.*
        FROM(
            SELECT
                contactClientId,
                MAX(contactId) as cid
            FROM
                contacts
            WHERE
                contactGroup = 4
            GROUP BY
                contactClientId
            ORDER BY
                NULL
        ) as tmp
        INNER JOIN contacts as c
            ON c.contactId = tmp.cid
            AND c.contactClientId = tmp.contactClientId 
    )  AS `contacts` ON `contactClientId` = `clients`.clientId
WHERE groupId = 4
ORDER BY `clients`.clientId

@GarethD its an optimization , basically filesort will not be used when grouping the data — Stephan, Sep 16 '13 at 15:10
Every day is a school day! I think it is bizarre, but there you go, it is still something new I've learnt. Thanks. — GarethD, Sep 16 '13 at 15:26
@GarethD that's why i love stackoverflow because you learn something new each day :D — Stephan, Sep 16 '13 at 15:50
@Stephan thanks, it works great separetly, but how do I attach it to the main query in the place of third JOIN? — user2104742, Sep 16 '13 at 18:27

Mysql GROUP BY and ORDER BY DESC

1 Answers1