title: How to make multiple LEFT JOINs with OR fully use a composite index? (part 2)
body: <p>It is for 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 more efficient like 52 rows? I also tried removing the <code>+1</code> part and the result is also 52.</p>