mysql - How to make multiple LEFT JOINs with OR fully use a composite index? (part 2) -

it system calculates how users scan fingerprints when enter/leave workplace. don't know how called in english. need determine if user late in morning, , if user leaves work early.

this tb_scan table contains date , time user scans fingerprint.

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 

it has 100,000+ rows, this

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 

and tb_workday table contains each date

create table `tb_workday` (   `wdpercode` varchar(6) default null,   `wdshift` varchar(1) default null,   `wddate` date default null ) engine=myisam default charset=latin1 

it has rows date sequence this:

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 

there tb_shift table containing shift time

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 

i want determine in each day, employee comes work late or leaves work early, , @ time.

select wdpercode,wddate,shiftbegin,shiftend,time(tlate.scscantime) wdlate,time(tearly.scscantime) wdearly tb_workday left join tb_shift   on wdshift=shiftcode left join tb_scan tlate    on wdpercode=tlate.scpercode   , tlate.scyear=year(wddate)   , tlate.scmonth=month(wddate)   , (tlate.scday=day(wddate)     or tlate.scday=day(wddate)+1)   , tlate.scscantime>=adddate(concat(wddate,' ',shiftbegin),interval if(shiftbegin2>shiftbegin,1,0) day)   , tlate.scscantime<=adddate(concat(wddate,' ',shiftmid),interval if(shiftbegin2>shiftmid,1,0) day) left join tb_scan tearly    on wdpercode=tearly.scpercode   , tearly.scyear=year(wddate)   , tearly.scmonth=month(wddate)   , (tearly.scday=day(wddate)     or tearly.scday=day(wddate)+1)   , tearly.scscantime>adddate(concat(wddate,' ',shiftmid),interval if(shiftbegin2>shiftmid,1,0) day)   , tearly.scscantime<adddate(concat(wddate,' ',shiftend),interval if(shiftbegin2>shiftend,1,0) day) 

here example of output:

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 

this adddate(concat(wddate,' ',shiftbegin),interval if(shiftbegin2>shiftbegin,1,0) day) employees work on night shift, has add 1 day shift time

the problem if create index scscantime, mysql refuses use comparison (>=,<=,>,<). please see thread why mysql not use index greater comparison?

because of created scyear, scmonth, , scday fields , combine them in index along scpercode. , have make sure calculates workers working in night shift have add or scday=day(wddate)+1 condition.

before added or condition, explain result 52 rows. when added or scday=day(wddate)+1 condition, explain result became 364 rows, means mysql did not use scday part of index. there way use whole index, explain result becomes more efficient 52 rows? tried removing +1 part , result 52.

first, better on posting question other. reason getting multiple records possibility of person clocking in , out multiple times in same day based on shifts. now, how resolve this.

in mysql, can inline variable declaration , assignments using "@" variables part of select clause. starting simple join work day shift table (and think understand now), @variables.

for each person, joined shift, pre-computing middle of shift occurs such same day vs next day. also, begin2 , end2 appear outliers possible clock-in vs clock-out. example: person 1 working shift 1. shift 1 defined given day of work

shiftcode   shiftbegin2  shiftbegin  shiftmid  shiftend  shiftend2         1     04:00:00     08:00:00  12:00:00  17:30:00  21:30:00  

so, interpreting if work on june 28, shift 1,

june 28 @ 4am earliest allowed clock-in time june 28 @ 8am actual beginning of shift june 28 @ 12pm (afternoon) middle of work day june 28 @ 5:30pm end of work day june 28 @ 9:30pm max expected clock-out recognized shift 

similarly, shift 2 wrap over-night

shiftcode   shiftbegin2  shiftbegin  shiftmid  shiftend  shiftend2         2     12:00:00     17:30:00  21:00:00  05:30:00  09:30:00  june 28 @ 12pm (afternoon) earliest allowed clock-in time june 28 @ 5:30pm actual beginning of shift june 28 @ 9pm middle of shift june 29 @ 5:30am (day roll-over) end of work day  june 29 @ 9:30am (day roll-over) max expected clock-out shift 

so, if correct, inner query pre-determining these ranges each person, ever have 1 record per person per work day regardless of how many scans via below.

select        wd.wdpercode,       wd.wdshift,       wd.wddate,       s.shiftbegin,       s.shiftend,       s.shiftbegin2,       s.shiftmid,       s.shiftend2,       @midday := if( s.shiftbegin < s.shiftmid, wd.wddate, date_add( wd.wddate, interval 1 day )) newmidday,       @endday := if( s.shiftbegin < s.shiftend, wd.wddate, date_add( wd.wddate, interval 1 day )) newendday,       cast( concat(wd.wddate, ' ', s.shiftbegin2 ) datetime ) earliestclockin,       cast( concat(wd.wddate, ' ', s.shiftbegin ) datetime ) beginshift,       cast( concat(@midday, ' ', s.shiftmid ) datetime ) midshift,       cast( concat( @endday, ' ', s.shiftend ) datetime ) endshift,       cast( concat( @endday, ' ', s.shiftend2 ) datetime ) maxclockout          ( select                @endday := '',                @midday := '' ) sqlvars,       tb_workday wd          join tb_shift s             on wd.wdshift = s.shiftcode 

the inline computing of @midday , @endday don't have worry joining scanned time clock table , keep adding 1 day in midst of else being considered. so, @ end of query, end like... notice between person 1 normal shift , person 2 night shift, computed end date shows roll-over dates too

wdpercode  wdshift  wddate      shiftbegin  shiftend  shiftbegin2  shiftmid  shiftend2  newmidday   newendday   earliestclockin   beginshift        midshift          endshift          maxclockout 000001     1        2010-10-10  08:00       17:30     04:00        12:00     21:30      2010-10-10  2010-10-10  2010-10-10 04:00  2010-10-10 08:00  2010-10-10 12:00  2010-10-10 17:30  2010-10-10 21:30:00 000001     1        2010-10-11  08:00       17:30     04:00        12:00     21:30      2010-10-11  2010-10-11  2010-10-11 04:00  2010-10-11 08:00  2010-10-11 12:00  2010-10-11 17:30  2010-10-11 21:30:00 000001     1        2010-10-12  08:00       17:30     04:00        12:00     21:30      2010-10-12  2010-10-12  2010-10-12 04:00  2010-10-12 08:00  2010-10-12 12:00  2010-10-12 17:30  2010-10-12 21:30:00 000001     1        2010-10-13  08:00       17:30     04:00        12:00     21:30      2010-10-13  2010-10-13  2010-10-13 04:00  2010-10-13 08:00  2010-10-13 12:00  2010-10-13 17:30  2010-10-13 21:30:00  000002     2        2010-10-10  17:30       05:30     12:00        21:00     09:30      2010-10-10  2010-10-11  2010-10-10 12:00  2010-10-10 17:30  2010-10-10 21:00  2010-10-11 05:30  2010-10-11 09:30:00 000002     2        2010-10-11  17:30       05:30     12:00        21:00     09:30      2010-10-11  2010-10-12  2010-10-11 12:00  2010-10-11 17:30  2010-10-11 21:00  2010-10-12 05:30  2010-10-12 09:30:00 000002     2        2010-10-12  17:30       05:30     12:00        21:00     09:30      2010-10-12  2010-10-13  2010-10-12 12:00  2010-10-12 17:30  2010-10-12 21:00  2010-10-13 05:30  2010-10-13 09:30:00 000002     2        2010-10-13  17:30       05:30     12:00        21:00     09:30      2010-10-13  2010-10-14  2010-10-13 12:00  2010-10-13 17:30  2010-10-13 21:00  2010-10-14 05:30  2010-10-14 09:30:00 

you remove columns query, included see / confirm values consideration of each row , date of work scheduled. abbreviated list still need is

select        wd.wdpercode,       @midday := if( s.shiftbegin < s.shiftmid, wd.wddate, date_add( wd.wddate, interval 1 day )) newmidday,       @endday := if( s.shiftbegin < s.shiftend, wd.wddate, date_add( wd.wddate, interval 1 day )) newendday,       cast( concat(wd.wddate, ' ', s.shiftbegin2 ) datetime ) earliestclockin,       cast( concat(wd.wddate, ' ', s.shiftbegin ) datetime ) beginshift,       cast( concat(@midday, ' ', s.shiftmid ) datetime ) midshift,       cast( concat( @endday, ' ', s.shiftend ) datetime ) endshift,       cast( concat( @endday, ' ', s.shiftend2 ) datetime ) maxclockout 

so, if above accurate, have clock in , out each person based on maximum range computed query more 1 record per date

wdpercode  earliestclockin    midshift          maxclockout 000001     2010-10-10 04:00   2010-10-10 12:00  2010-10-10 21:30:00 000002     2010-10-10 12:00   2010-10-10 21:00  2010-10-11 09:30:00 

so here, doing join scan times dates within earliest clock in , max clock out , using midshift basis of determining if clocked in late vs leaving early. added min() , max() arrival , departure given person / shift confirm , should seeing.

the purpose of max( if() ) capture late / status if have happened. since group per shift, first record (clock in) might late , want time, second record clocking out not applicable via mid-shift time , blank. detecting departure shift.

select       perperson.wdpercode,       perperson.beginshift,       perperson.endshift,       min( ts.scscantime ) arrival,       max( ts.scscantime ) departure,       max( if( ts.scscantime > perperson.beginshift                     , ts.scscantime <= perperson.midshift, ts.scscantime, "" )) latearrival,       max( if( ts.scscantime > perperson.midshift            , ts.scscantime < perperson.endshift, ts.scscantime, "" )) earlydepart          ( select               wd.wdpercode,               @midday := if( s.shiftbegin < s.shiftmid, wd.wddate,                   date_add( wd.wddate, interval 1 day )) newmidday,               @endday := if( s.shiftbegin < s.shiftend, wd.wddate,                   date_add( wd.wddate, interval 1 day )) newendday,               cast( concat(wd.wddate, ' ', s.shiftbegin2 ) datetime ) earliestclockin,               cast( concat(wd.wddate, ' ', s.shiftbegin ) datetime ) beginshift,               cast( concat(@midday, ' ', s.shiftmid ) datetime ) midshift,               cast( concat( @endday, ' ', s.shiftend ) datetime ) endshift,               cast( concat( @endday, ' ', s.shiftend2 ) datetime ) maxclockout                          ( select                       @endday := '',                       @midday := '' ) sqlvars,               tb_workday wd                  join tb_shift s                     on wd.wdshift = s.shiftcode ) perperson          join tb_scan ts             on perperson.wdpercode = ts.scpercode             , ts.scscantime >= perperson.earliestclockin             , ts.scscantime <= perperson.maxclockout    group       perperson.wdpercode,       perperson.beginshift; 

i created tables , sample data provided via (of of data did not match sample dates , ranges, adjusted so).

create table `tb_scan` (   `scpercode` varchar(6) default null,   `scscantime` datetime,   key `all` (`scyear`,`scmonth`,`scday`) ) engine=myisam default charset=latin1;  insert tb_scan  ( scpercode, scscantime )  values ( '000001', '2010-10-10 08:02:00' ), ( '000001', '2010-10-10 17:33:00' ), ( '000001', '2010-10-11 07:48:00' ), ( '000001', '2010-10-11 17:29:00' ), ( '000001', '2010-10-12 08:04:00' ), ( '000001', '2010-10-12 17:28:00' ), ( '000002', '2010-10-10 17:31:00' ), ( '000002', '2010-10-11 05:35:00' ), ( '000002', '2010-10-11 17:28:00' ), ( '000002', '2010-10-12 05:29:00' ), ( '000002', '2010-10-12 17:32:00' ), ( '000002', '2010-10-13 05:27:00' );  create table `tb_workday` (   `wdpercode` varchar(6) default null,   `wdshift` varchar(1) default null,   `wddate` date default null ) engine=myisam default charset=latin1;  insert tb_workday  ( wdpercode, wdshift, wddate ) values ( '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' );   create table `tb_shift` (   `shiftcode` varchar(1) default null,   `shiftbegin2` varchar(8) default null,   `shiftbegin` varchar(8) default null,   `shiftmid` varchar(8) default null,   `shiftend` varchar(8) default null,   `shiftend2` varchar(8) default null ) engine=myisam default charset=latin1;  insert tb_shift ( shiftcode, shiftbegin2, shiftbegin, shiftmid, shiftend, shiftend2 ) values ( '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' ); 

the sample data shows each person 1: arrive late, 2: depart early, 3: arrive late , depart early.

wdpercode  beginshift         endshift           arrival            departure          latearrival        earlydepart 000001     2010-10-10 08:00   2010-10-10 17:30   2010-10-10 08:02   2010-10-10 17:33   2010-10-10 08:02 000001     2010-10-11 08:00   2010-10-11 17:30   2010-10-11 07:48   2010-10-11 17:29                      2010-10-11 17:29 000001     2010-10-12 08:00   2010-10-12 17:30   2010-10-12 08:04   2010-10-12 17:28   2010-10-12 08:04   2010-10-12 17:28  000002     2010-10-10 17:30   2010-10-11 05:30   2010-10-10 17:31   2010-10-11 05:35   2010-10-10 17:31 000002     2010-10-11 17:30   2010-10-12 05:30   2010-10-11 17:28   2010-10-12 05:29                      2010-10-12 05:29 000002     2010-10-12 17:30   2010-10-13 05:30   2010-10-12 17:32   2010-10-13 05:27   2010-10-12 17:32   2010-10-13 05:27 

for optimizing query, change index on scan table

create table `tb_scan` (   `scpercode` varchar(6) default null,   `scscantime` datetime,   key `persondate` (`scpercode`, `scscantime` )