MySql - How can I improve this query?

Question

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!

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;

Answer 2

Try this:

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