Как сделать так, чтобы несколько LEFT JOIN с OR полностью использовали составной индекс? (часть 2)
Он предназначен для системы, которая рассчитывает, как пользователи сканируют свои отпечатки пальцев при входе / выходе с рабочего места. Я не знаю, как это называется по-английски. Мне нужно определить, опаздывает ли пользователь по утрам и рано ли уходит с работы.
этоtb_scan
Таблица содержит дату и время, когда пользователь сканирует отпечаток пальца.
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
В нем более 100 000 строк, как-то так
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
И этоtb_workday
таблица содержит каждую дату
CREATE TABLE `tb_workday` (
`wdpercode` varchar(6) DEFAULT NULL,
`wdshift` varchar(1) DEFAULT NULL,
`wddate` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
У этого есть строки с последовательностью даты как это:
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
Есть еще одноtb_shift
таблица, содержащая время смены
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
Я хочу определить, является ли работник каждый день на работу поздно или уходит с работы рано и в какое время.
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>=ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY)
AND tlate.scscantime<=ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2>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>ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2>shiftmid,1,0) DAY)
AND tearly.scscantime<ADDDATE(CONCAT(wddate,' ',shiftend),INTERVAL IF(shiftbegin2>shiftend,1,0) DAY)
Вот пример вывода:
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
этотADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY)
для сотрудников, работающих в ночную смену, поэтому необходимо добавить 1 день в смену
Проблема в том, если я создаю индекс дляscscantime
MySQL отказывается использовать его для сравнения (>=
,<=
,>
,<
). Пожалуйста, посмотрите эту темуПочему MySQL не использует индекс для сравнения больше?
Из-за этого я создалscyear
, scmonth
, а такжеscday
поля и объединить их в индексе вместе сscpercode
, И я должен убедиться, что он рассчитывает на работников, работающих в ночную смену, поэтому я должен добавить его сOR scday=day(wddate)+1
состояние.
Прежде чем я добавил условие ИЛИ,EXPLAIN
результат составил 52 строки. Но когда я добавилOR scday=day(wddate)+1
состояние,EXPLAIN
результат стал 364 строк, это означает, что MySQL не использовал scday часть индекса. Есть ли способ использовать весь индекс, поэтомуEXPLAIN
результат становится более эффективным, как 52 строки? Я также попытался удалить+1
часть и результат также 52.