Finding Specific Rows in Table Using ROW() Causing Query to Execute Slowly in mySQL-CodePudding

I am trying to run a query to find matching values in two separate tables, but I am not happy with my execution time. I was wondering if there was some different syntax that I could use instead of ROW() that would provide better performance.

Current Query:

SELECT de.rowID, de.EffRate, li.SessionID FROM tblClockDetail li
  INNER JOIN tblCardDetail de
    ON de.TimeIn = li.LogInTime AND 
       de.TimeOut = li.LogOutTime AND 
       de.Employee = li.Employee
WHERE ROW(de.Employee, li.SessionID) IN (SELECT * FROM tblSessionDetails)

Any help is appreciated!

CodePudding user response：

It turns out all I needed were some additional WHERE checks:

WHERE ROW(de.Employee, li.SessionID) IN (SELECT * FROM tblSessionDetails) 
AND li.SessionID IS NOT NULL AND
li.SessionID != 0

This drastically reduced my execution time.

CodePudding user response：

"Row constructors", historically, have been poorly optimized.

See if a simple JOIN works better:

SELECT de.rowID, de.EffRate, li.SessionID
    FROM tblClockDetail AS li
    JOIN tblCardDetail AS de
      ON  de.TimeIn = li.LogInTime
      AND de.TimeOut = li.LogOutTime
      AND de.Employee = li.Employee
    JOIN tblSessionDetails AS td
      ON de.Employee = td.Employee
     AND li.SessionID = td.SessionID;

with these composite indexes:

de:  INDEX(TimeIn, TimeOut, Employee)
li:  INDEX(LogInTime, LogOutTime, Employee, SessionID)
td:  INDEX(Employee, SessionID)

For further discussion, please provide SHOW CREATE TABLE and EXPLAIN SELECT ... and how big the tables are.