CODEWITHSUNDEEP
mysqlsql
ccdavies
ccdavies

Reputation: 1606

Very slow NOT EXISTS query issue

Can anyone tell me if there are any obvious issues as to why the following query is taking 1.3seconds to complete?

Is there a way I can speed this query up?

SELECT COUNT(DISTINCT jud.rel_id) AS count_result
    FROM exp_judging AS jud
    LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
    WHERE (jud.judge_id != 781 OR jud.judge_id IS NULL)
    AND jud.pre = 1
    AND sub.member_group = 5
    AND NOT EXISTS (SELECT sub2.entry_id 
                            FROM exp_judging AS jud2
                    LEFT JOIN exp_submissions AS sub2 ON jud2.rel_id = sub2.id 
                    WHERE (jud2.judge_id = 781)
                    AND jud2.pre = 1
                    AND sub2.member_group = 5
                    AND jud2.rel_id = jud.rel_id)

Upvotes: 2

Views: 579

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want to count the distinct jud.rel_id subject to some conditions:

  1. jud.pre = 1
  2. sub.member_group = 5
  3. The group does not contain judge 781

Answering some question like "How many "pre" judges in group 5 have never had a submission with judge 781?". If so, there might be an easier way to write the query.

In any case, your query, rewritten so I can read it is:

SELECT COUNT(DISTINCT jud.rel_id) AS count_result
FROM exp_judging jud LEFT JOIN
     exp_submissions sub
     ON jud.rel_id = sub.id 
WHERE (jud.judge_id != 781 OR jud.judge_id IS NULL) AND
      jud.pre = 1 AND
      sub.member_group = 5 AND
      NOT EXISTS (SELECT sub2.entry_id 
                  FROM exp_judging jud2 LEFT JOIN
                       exp_submissions sub2
                       ON jud2.rel_id = sub2.id 
                  WHERE jud2.judge_id = 781 AND
                        jud2.pre = 1 AND
                        sub2.member_group = 5 AND
                        jud2.rel_id = jud.rel_id
                 )

You want to use indexes to speed the query. My best guess at indexes for this query are:

exp_judging(judge_id, pre, rel_id)
exp_submissions(id, member_group)

Upvotes: 0

Joseph B
Joseph B

Reputation: 5669

I have re-written the whole query to 1) first, select exp_judging.rel_id's that do not have a row with judge_id=781, and 2) then, for these rel_id's the count is obtained from the exp_judging table.

EDIT:

SELECT 
    COUNT(DISTINCT jud0.rel_id)
FROM
    exp_judging AS jud0
    INNER JOIN (
        SELECT 
            jud.rel_id as rel_id,
            SUM(
                CASE jud.judge_id
                    WHEN 781 THEN 1
                    ELSE 0
                END) sum_judge_id
        FROM 
            exp_judging AS jud
            INNER JOIN exp_submissions AS sub
                ON jud.rel_id = sub.id 
        WHERE 
            jud.pre = 1
            AND sub.member_group = 5
        GROUP BY 
            jud.rel_id) judge_id_sums
        ON jud0.rel_id = judge_id_sums.rel_id 
WHERE 
    judge_id_sums.sum_judge_id = 0;

The inner join exp_judging and exp_submissions will count jud.rel_id only if jud2.rel_id exists in exp_submissions (as sub2.id). However, if you do want to count all jud.rel_id (even if it does not exist in exp_submissions), then LEFT JOIN may be used.

Upvotes: 1

Related Questions