How to make multiple LEFT JOINs with OR fully use a composite index?
<p>I am working on a timekeeping system and trying to determine that for each person, are they early or late for their scheduled shift.  The "TB_Scan" table originally had the person code and scan time which is a datetime field.  However, due to querying issues I was having, decided to add the scYear, scMonth and scDay fields thinking it might help.</p>

<p>It is a system that calculates how the users scan their fingerprints when they enter/leave the workplace. I don't know how it is called in English. I need to determine if the user is late in the morning, and if the user leaves work early.</p>

<p>This <code>tb_scan</code> table contains date and time a user scans a fingerprint.</p>

<pre><code>CREATE TABLE `tb_scan` (
  `scpercode` varchar(6) DEFAULT NULL,
  `scyear` varchar(4) DEFAULT NULL,
  `scmonth` varchar(2) DEFAULT NULL,
  `scday` varchar(2) DEFAULT NULL,
  `scscantime` datetime,
  KEY `all` (`scyear`,`scmonth`,`scday`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
</code></pre>

<p>It has 100,000+ rows, something like this</p>

<pre><code>scpercode scyear scmonth scday     scdateandtime
000001    2010      10     10      2016-01-10 08:02:00
000001    2010      10     10      2016-01-02 17:33:00
000001    2010      10     11      2016-01-11 07:48:00
000001    2010      10     11      2016-01-11 17:29:00
000002    2010      10     10      2016-01-10 17:31:00
000002    2010      10     10      2016-01-02 17:28:00
000002    2010      10     11      2016-01-11 05:35:00
000002    2010      10     11      2016-01-11 05:29:00
</code></pre>

<p>And this <code>tb_workday</code> table contains each date</p>

<pre><code>CREATE TABLE `tb_workday` (
  `wdpercode` varchar(6) DEFAULT NULL,
  `wdshift` varchar(1) DEFAULT NULL,
  `wddate` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
</code></pre>

<p>It has rows with date sequence like this:</p>

<pre><code>wdpercode  wdshift wddate
000001     1       2010-10-10
000001     1       2010-10-11
000001     1       2010-10-12
000001     1       2010-10-13
000002     2       2010-10-10
000002     2       2010-10-11
000002     2       2010-10-12
000002     2       2010-10-13
</code></pre>

<p>There is another <code>tb_shift</code> table containing shift time</p>

<pre><code>CREATE TABLE `tb_shift` (
  `shiftcode` varchar(1) DEFAULT NULL,
  `shiftbegin2` varchar(4) DEFAULT NULL,
  `shiftbegin` varchar(4) DEFAULT NULL,
  `shiftmid` varchar(4) DEFAULT NULL,
  `shiftend` varchar(4) DEFAULT NULL,
  `shiftend2` varchar(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

shiftcode   shiftbegin2  shiftbegin  shiftmid  shiftend  shiftend2
        1     04:00:00     08:00:00  12:00:00  17:30:00  21:30:00 
        2     12:00:00     17:30:00  21:00:00  05:30:00  09:30:00
</code></pre>

<p>I want to determine that in each day, is the employee comes to work late or leaves work early, and at what time.</p>

<pre><code>SELECT wdpercode,wddate,shiftbegin,shiftend,time(tlate.scscantime) wdlate,time(tearly.scscantime) wdearly
FROM tb_workday
LEFT JOIN tb_shift
  ON wdshift=shiftcode
LEFT JOIN tb_scan tlate 
  ON wdpercode=tlate.scpercode
  AND tlate.scyear=year(wddate)
  AND tlate.scmonth=month(wddate)
  AND (tlate.scday=day(wddate)
    OR tlate.scday=day(wddate)+1)
  AND tlate.scscantime&gt;=ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2&gt;shiftbegin,1,0) DAY)
  AND tlate.scscantime&lt;=ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2&gt;shiftmid,1,0) DAY)
LEFT JOIN tb_scan tearly 
  ON wdpercode=tearly.scpercode
  AND tearly.scyear=year(wddate)
  AND tearly.scmonth=month(wddate)
  AND (tearly.scday=day(wddate)
    OR tearly.scday=day(wddate)+1)
  AND tearly.scscantime&gt;ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2&gt;shiftmid,1,0) DAY)
  AND tearly.scscantime&lt;ADDDATE(CONCAT(wddate,' ',shiftend),INTERVAL IF(shiftbegin2&gt;shiftend,1,0) DAY)
</code></pre>

<p>Here is the example of an output:</p>

<pre><code>wdpercode wddate      shiftbegin  shiftend  wdlate    wdearly
000001    2016-01-10  08:00:00    17:30:00  08:02:00  (null)
000001    2016-01-11  08:00:00    17:30:00  (null)    17:29:00
000002    2016-01-11  17:30:00    05:30:00  17:31:00  (null)
000002    2016-01-11  17:30:00    05:30:00  (null)    05:29:00
</code></pre>

<p>this <code>ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2&gt;shiftbegin,1,0) DAY)</code> is for employees who work on night shift, so it has to add 1 day into the shift time</p>

<p>The problem is if I create an index for <code>scscantime</code>, MySQL refuses to use it for comparison (<code>&gt;=</code>,<code>&lt;=</code>,<code>&gt;</code>,<code>&lt;</code>). Please see this thread <a href="https://stackoverflow.com/questions/4691799/why-does-mysql-not-use-an-index-for-a-greater-than-comparison">Why does MySQL not use an index for a greater than comparison?</a></p>

<p>Because of this I created the <code>scyear</code>, <code>scmonth</code>, and <code>scday</code> fields and combine them in an index along with <code>scpercode</code>. And I have to make sure it calculates for workers working in night shift too so I have to add it with <code>OR scday=day(wddate)+1</code> condition.</p>

<p>Before I added the OR condition, the <code>EXPLAIN</code> result was 52 rows. But when I added the <code>OR scday=day(wddate)+1</code> condition, the <code>EXPLAIN</code> result became 364 rows, that means MySQL did not use scday part of the index. Is there any way to use the whole index, so the <code>EXPLAIN</code> result becomes 52 rows? I also tried removing the <code>+1</code> part and the result is also 52.</p>