Mysql -Which Column table the index should be added

indexMySQL

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.)

Best Answer

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).

Related Question