Optimal single query for checking if record exists in either/or table

Question

This is a bit of a weird question, so the best way to ask it is with an example. I have a list of customers. I want to get any customer who has a corresponding entry in either the CourseHistory table or the Access table (or both).

I want an optimal single query (no subqueries) that fetches these customers. I came up with

SELECT
   c.cusid
FROM
   Customers c
   CROSS JOIN Realms r
   LEFT JOIN Course.CourseHistory ch ON (c.cusid = ch.cusid)
   LEFT JOIN Access a ON (c.cusid = a.cusid AND r.realmid = a.realmid)
WHERE
   realmname = 'Course'
   AND COALESCE(chid, accid)

This works but it is noticeably slow, probably because it has to do a full scan of Customers. Since either CourseHistory or Access can be null and the result still be valid, they have to be left joined. Is there a more correct way to do this query?

Answer 1

Get rid of that CROSS JOIN to Realms and INNER JOIN that table to Access instead.

SELECT
   c.cusid
FROM
   Customers c
   LEFT JOIN Course.CourseHistory ch ON (c.cusid = ch.cusid)
   LEFT JOIN Access a 
       INNER JOIN realms r
           ON a.realmid = r.realmid
               AND r.realmname = 'Course'
       ON c.cusid = a.cusid
WHERE
   COALESCE(chid, accid)

Answer 2

Here is your original query

SELECT  
   c.cusid  
FROM  
   Customers c  
   CROSS JOIN Realms r  
   LEFT JOIN Course.CourseHistory ch ON (c.cusid = ch.cusid)  
   LEFT JOIN Access a ON (c.cusid = a.cusid AND r.realmid = a.realmid)  
WHERE  
   realmname = 'Course'  
   AND COALESCE(chid, accid)  
; 

From you comments, I realize this now

• Realm can reach Access
• Access reach customer, but you will not need to
• Access can reach Course.CourseHistory via cusid

Given this path, here is the refactored query

SELECT r.cusid
FROM
(SELECT realmid FROM Realms WHERE realmname = 'Course') r
LEFT JOIN
(SELECT realmid,cusid,accid FROM Access) a ON r.realmid=a.realmid
LEFT JOIN
(SELECT cusid FROM Course.CourseHistory) ch ON a.cusid=ch.cusid
WHERE COALESCE(chid, accid);

You will need the following indexes

ALTER TABLE Realms ADD INDEX realmname_realmid_ndx (realmname,realmid);
ALTER TABLE Access ADD INDEX realmid_cusid_accid_ndx (realmid,cusid,accid);

Give it a Try !!!