Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following table: http://pastebin.com/XH6FvhZ8

and the following query:

SELECT *
FROM (SELECT *
      FROM `job_logs`
      ORDER BY id DESC) AS job_logs_tmp
WHERE (job_logs_tmp.user_id = ? AND job_logs_tmp.workflow_state = ?)
GROUP BY chain, subchain, job_type

here is the explain of this query: http://pastebin.com/AjXgzRBA

this query usually takes over 4K ms to execute, any ideas how I can speed the query execution? I have no problem adding indexes to table if needed

thanks!

share|improve this question
1  
You want a greatest-n-per-group query. Search the SO site for this tag and you'll find plenty of similar queries and answers. –  ypercube Apr 28 '12 at 15:00
    
remove the ORDER BY id DESC... the group by will destroy the ordering anyway. –  Ben Apr 29 '12 at 17:21
    
@Ben: Not in MySQL ... –  ypercube Apr 29 '12 at 17:29
    
Thanks @ypercube, didn't know! –  Ben Apr 29 '12 at 19:10

1 Answer 1

What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type 

SELECT * in a grouping query will not work in most (if not all) SQL implementations.


UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type 
)

This query is deterministic and should work with most SQL dialects.


Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;
share|improve this answer
    
In MySQL it works. The query would return one row per group (the one with highest id). –  ypercube Apr 28 '12 at 17:00
    
@ypercube: I missed this possibility from other implementations! –  Olivier Jacot-Descombes Apr 28 '12 at 17:35
1  
@ypercube: "works" is relative. It will return non-deterministic results. –  a_horse_with_no_name Apr 29 '12 at 17:55
    
@a_horse_with_no_name: I don't disagree on that. I should have said something like: "It works, now, but one never knows when it will break." –  ypercube Apr 30 '12 at 12:28
    
I don't know if my new query solves the performance problems (it depends on how the query optimizer works), but it should solve the non-deterministic problem mentioned by a_horse_with_no_name. –  Olivier Jacot-Descombes Apr 30 '12 at 13:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.