How to optimise below sql query?

Question

What I'm doing here is getting the reservations and the user which are duplicate. Here reservation ticket can not be printed twice. If a user prints a ticket tracker table updates with that record. If a user prints the same ticket twice it's marked as a duplicate. What subquery does here is return some reservation ids which are marked as duplicates.

    SELECT t1.id AS res_id,
       t1.tx_id,
       t1.tx_date,
       t1.bx_date,
       t1.method,
       t1.theater_id,
       t1.showtime_id,
       t1.category_id,
       t1.amount,
       t1.fname,
       t1.status,
       t1.mobile,
       u.username,
       t2.*
FROM `reservation` AS t1
INNER JOIN
  ( SELECT *
   FROM `tracker`
   WHERE reservation_id IN
       ( SELECT reservation_id
        FROM `tracker`
        GROUP BY reservation_id HAVING ( METHOD = 1
                                        AND TYPE = 0
                                        AND COUNT(*) > 1 )
        OR ( METHOD = 1
            AND TYPE = 1
            AND COUNT(*) > 1 )
        OR ( METHOD = 2
            AND TYPE = 2
            AND COUNT(*) > 0 )
        OR ( METHOD = 3
            AND TYPE = 0
            AND COUNT(*) > 0 )
        OR ( METHOD = 3
            AND TYPE = 1
            AND COUNT(*) > 1 )
        OR ( METHOD = 3
            AND TYPE = 3
            AND COUNT(*) > 0 )) ) AS t2 ON t1.id = t2.reservation_id
INNER JOIN `users` AS u ON u.id = t2.user_id
WHERE t2.resolved = 0
  AND t2.duplicate = 1
ORDER BY t2.issue_date DESC, t1.id DESC

EXPLAIN Command of the above query.

enter image description here

What should I do? If I'm index which keys should I use? How can I decide which keys to index? I know subquery slows me down What procedures should I follow to eliminate the slowness?

Explain :: http://dev.mysql.com/doc/refman/5.0/en/explain-output.html — Sashi Kant, Jan 04 '13 at 09:31
Check this post if you want to know about key optimization - http://stackoverflow.com/questions/14064489/mysql-composite-indexing-with-tenant-id/14088424#14088424 — Joddy, Jan 04 '13 at 09:45
Check this post if you need to know about FORCE INDEX - http://stackoverflow.com/questions/14137549/how-to-interpret-the-output-of-mysql-explain/14137997#14137997 — Joddy, Jan 04 '13 at 09:47
The `GROUP BY reservation_id HAVING ...` part seems fishy. Is `reservation_id` the primary key of `tracker`? If not, the `HAVING method=1 AND type=0` will choose arbitrary values for these columns because they are not included in the `GROUP BY`. — ypercubeᵀᴹ, Jan 04 '13 at 10:37
@ypercube ... and if `reservation_id` _is_ the primary key, no `reservation_id` will be found where `COUNT(*) > 1` — Terje D., Jan 04 '13 at 14:17
Then your query is broken. Describe what you want to achieve, give table with sample data, so someone can help. — ypercubeᵀᴹ, Jan 04 '13 at 14:18

score 2 · Accepted Answer · edited Jan 07 '13 at 05:30

In MySQL, exists subqueries are often faster than in subqueries. You might try:

SELECT t1.id AS res_id, t1.tx_id, t1.tx_date, t1.bx_date,t1.method, t1.theater_id, t1.showtime_id,
       t1.category_id, t1.amount, t1.fname, t1.status, t1.mobile, u.username, t2.*
FROM `reservation` t1 INNER JOIN
     (SELECT *
      FROM `tracker` t
      WHERE EXISTS (SELECT 1
                    FROM `tracker` t3
                    where t3.reservation_id = t.reservation_id
                    GROUP BY reservation_id
                    HAVING (METHOD = 1 AND TYPE = 0 AND COUNT(*) > 1) OR
                           (METHOD = 1 AND TYPE = 1 AND COUNT(*) > 1) OR
                           (METHOD = 2 AND TYPE = 2 AND COUNT(*) > 0) OR
                           (METHOD = 3 AND TYPE = 0 AND COUNT(*) > 0) OR
                           (METHOD = 3 AND TYPE = 1 AND COUNT(*) > 1) OR
                           (METHOD = 3 AND TYPE = 3 AND COUNT(*) > 0)
                   )
     ) t2
     ON t1.id = t2.reservation_id INNER JOIN
     `users` AS u ON u.id = t2.user_id
WHERE t2.resolved = 0 AND t2.duplicate = 1
ORDER BY t2.issue_date DESC, t1.id DESC

I notice the subquery is using Hidden Columns in the having clause. It may not be doing what you expect. Normally, the query would include method and type in the group by clause or have an expression such as max(Method).

if you can please explain what have you done in the subquery. I would really love to learn. — Techie, Jan 07 '13 at 05:40
I simply replaced the construct `in (subquery)` with `exists (select 1 from (subquery with correlated where clause)`. This is a hack because MySQL sometimes does not optimize `in` as efficiently as the exists. — Gordon Linoff, Jan 07 '13 at 14:06

How to optimise below sql query?

1 Answers1