Search code examples
mysqlsqlexplain

making it so primary key + constant can use index

I'm working with some time clock software and am trying to find evidence that the time clock logs have been tampered with. Here's my query:

EXPLAIN SELECT log1.userName, log1.logID
FROM timeLog log1
JOIN timeLog log2 ON log1.logID = log2.logID + 1
JOIN timeLog log3 ON log1.logID = log3.logID - 1
WHERE log1.timeIN NOT BETWEEN log2.timeIN AND log3.timeIN;

The problem is that the query is taking a long time. Here's the EXPLAIN:

enter image description here

So the type for log2 and log3 is ALL - not eq_ref. If they were being compared to logID instead of logID +/- constant they'd be eq_ref with 1 row vs. 259942 each.

Is it possible to take advantage of an index when looking for an indexed value added to a scalar constant?

Solution

  • Have you tried rewriting the ON conditions as:

    SELECT log1.userName, log1.logID
  FROM timeLog log1
  JOIN timeLog log2 ON log2.logID = log1.logID - 1
  JOIN timeLog log3 ON log3.logID = log1.logID + 1
 WHERE log1.timeIN NOT BETWEEN log2.timeIN AND log3.timeIN;

    Generally you want to keep the indexed column unadulterated on one side of the =, I tend to go for the left side.