0

I have written this query (which returns only 67 records, which is fine)

The problem is with the query time, the Query took 0.0042 seconds which is slow, I heard that indexes can help fasten the query, shall I add indexes on UserID or OfficeID?

SELECT Distinct(u.UserID),
       u.Role,
       u.FirstName,
       u.LastName,
       u.Email 
FROM   Users as u,
       Hires as h, 
       Contractors as c, 
       ContractorCompanies as cc 
where  (u.UserID = h.User_ID AND h.Offices_OfficeID = 3)  
OR     (u.UserID = c.User_ID AND c.Offices_OfficeID = 3)  
OR     (u.UserID = cc.User_ID AND cc.Offices_OfficeID = 3)

Updated: When I added an index on UserID the Query took 0.0066 seconds.)

  • 1
    DISTINCT is not function by the way. You can remove the parentheses around u.UserID. – ypercubeᵀᴹ Jan 17 '18 at 11:41
  • @ypercubeᵀᴹ thanks, I did that still nothing changes in query time Query took 0.0042 seconds. – user580950 Jan 17 '18 at 11:44
  • 1
    Yeah, that is irrelevant to any speed issue. But how is 0.0042 slow? How big are the tables? – ypercubeᵀᴹ Jan 17 '18 at 11:51
  • 1
    But forget any speed issue for a moment. Is the query returning correct results? The OR construction seems odd and wrong. – ypercubeᵀᴹ Jan 17 '18 at 11:52
  • 1
    Have you tried actual JOIN - ON syntax? – McNets Jan 17 '18 at 12:01
2

There are several semantic issues with the structure of the query.

  • Using OR with conditions from various different tables, in combination with the Cartesian product of these 4 tables looks very weird. I find unlikely that this is what you actually want.
  • Using DISTINCT. That's probably needed only because of the weird structure mentioned above.

So, I suggest a few things:

  • Get rid of the DISTINCT
  • Rewrite the joins using proper JOIN .. ON syntax or - probably makes more sense in this case - rewrite using EXISTS subqueries which can be combined with OR.

The query becomes:

SELECT u.UserID,
       u.Role,
       u.FirstName,
       u.LastName,
       u.Email 
FROM   Users AS u
WHERE  EXISTS 
         ( SELECT * 
           FROM Hires as h
           WHERE u.UserID = h.User_ID AND h.Offices_OfficeID = 3
         )
   OR  EXISTS
         ( SELECT * 
           FROM Contractors AS c
           WHERE u.UserID = c.User_ID AND c.Offices_OfficeID = 3
         )
   OR  EXISTS
         ( SELECT * 
           FROM ContractorCompanies AS cc
           WHERE u.UserID = cc.User_ID AND cc.Offices_OfficeID = 3
         )
 ;

If the above query returns the wanted results, then add an index on (Offices_OfficeID, User_ID) on each of the 3 tables.

I assume you already have an index on Users (UserID).

  • Plain JOIN ON effectively filter out rows that have no corresponding records among all joined tables. – Kondybas Jan 17 '18 at 13:03
  • 1
    @Kondybas not sure what your point is. – ypercubeᵀᴹ Jan 17 '18 at 15:29
  • WHERE EXISTS (SELECT... is an emulation of the JOIN ON in that specific case. But execution plan will be significantly worst. – Kondybas Jan 17 '18 at 20:09
  • 1
    @Kondybas I still don't get your point. "worst" compared to what? And how do you know? Did you make performance tests? – ypercubeᵀᴹ Jan 18 '18 at 9:22
0

This syntax is more straightforward:

SELECT u.UserID
     , u.Role
     , u.FirstName
     , u.LastName
     , u.Email 
  FROM Users as u
  JOIN Hires as h                ON h.User_ID = u.UserID
  JOIN Contractors as c          ON c.User_ID = u.UserID 
  JOIN ContractorCompanies as cc ON cc.User_ID = u.UserID 
 WHERE h.Offices_OfficeID = 3
    OR c.Offices_OfficeID = 3
    OR cc.Offices_OfficeID = 3
;

General indexing rules are:

  • if field is used for JOINs it should be indexed in both joined tables
  • if table is filtered by WHERE or sorted by ORDER BY involved fields should be indexed.
  • order involved fields are mentioned in the index definition is significant and depends on fields cardinality. More selective fields should be placed first.

In your case all tables need the fields UserID and Offices_OfficeID to be indexed.

N.B.

0.0066 sec/query isn't slow. This is the usual value for the fast/optimized queries. You have to profile your query to detect the most time consumpting phase:

SET profiling=1;
<YOUR QUERY HERE>
SHOW PROFILE;
SET profiling=0;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.