Optimal single query to check for a record in / or table

Question

This is a bit of a strange question, so the best way to ask it is to give an example. I have a list of clients. I want to get any client that has a corresponding entry in the CourseHistory table or in the Access table (or both).

I want the optimal single query (without subqueries) that retrieves these clients. I figured it out

 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 run a full Customers scan. Since either CourseHistory or Access can be zero, and the result will still be valid, they will need to be connected. Is there a better way to make this request?

Answer 1

Get rid of this CROSS JOIN in Realms and INNER JOIN so that the table is used 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 request

 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 your comments, I understand it now

• Area can reach Access
• Access to the client, but you do not need
• Access can reach Course.CourseHistory via cusid.

Given this path, here is a refactored request

 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 !!!

Answer 3

Your current query starts with the Cartesian product Customers x Realms and must return too many duplicates. There should also be many duplicates that come from associations on Course.CourseHistory and Access , if there is more than one entry for each client.

The following query should be more efficient. UNION in the request implicit DISTINCT is used, that is, all identifiers are unique. In addition, the query allows the MySQL optimizer to capitalize on table statistics and organize connections in the optimal order.

 SELECT cuid FROM Customers c JOIN Course.CourseHistory ch USING (cuid) UNION SELECT cuid FROM Realms r JOIN Access a USING (realmid) JOIN Customers c USING (cuid) WHERE r.realmname = 'Course'; 

Answer 4

It seems you want to collect all the client identifiers from CourseHistory and Access (within the Course area) and get the clients that match these identifiers. Here is a query that does just that.

 SELECT c.cusid FROM Customers c INNER JOIN ( SELECT ch.cusid FROM Course.CourseHistory AS ch UNION SELECT a.cusid FROM Access a INNER JOIN Realms r ON r.realmid = a.realmid AND r.realmname = 'Course' ) AS ids ON c.cusid = ids.cusid 

If you really only need a client identifier, and the client table will contain all existing client identifiers, then you can refuse the external selection and just use the internal UNION.

If you really need one request, you will have to agree to something even worse. This, at least, is an improvement over the original query:

 SELECT c.cusid FROM Customers c LEFT JOIN Course.CourseHistory ch ON (c.cusid = ch.cusid) LEFT JOIN Access a ON (c.cusid = a.cusid) LEFT JOIN Realms r ON (r.realmid = a.realmid AND realmname = 'Course') WHERE AND chid IS NOT NULL OR realmname IS NOT NULL