IN clause not using index (part 2)

Question

This time it's about left join. These are table definitions:

CREATE TABLE `tb_workday` (
  `id` int(11) DEFAULT NULL,
  `wddate` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

tb_workday content sample:

+----+------------+
| id | wddate     |
+----+------------+
|  1 | 2016-06-01 |
|  2 | 2016-06-02 |
|  3 | 2016-06-03 |
|  4 | 2016-06-04 |
|  5 | 2016-06-05 |

and tb_time

CREATE TABLE `tb_t` (
  `TBegDate` date NOT NULL,
  `TBegTime` time DEFAULT NULL,
  PRIMARY KEY (`TBegDate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

tb_time content sample:

+------------+----------+
| TBegDate   | TBegTime |
+------------+----------+
| 2016-01-01 | 19:00:33 |
| 2016-01-02 | 22:23:33 |
| 2016-01-03 | 17:43:33 |
| 2016-01-04 | 00:02:33 |
| 2016-01-05 | 19:00:33 |
| 2016-01-06 | 06:28:33 |
| 2016-01-07 | 03:41:33 |
| 2016-01-08 | 11:24:33 |

This is the result I wanted. I want the table to show the tb_time result of wddate and wddate+1:

+----+------------+------------+----------+
| id | wddate     | TBegDate   | TBegTime |
+----+------------+------------+----------+
|  1 | 2016-06-01 | 2016-06-01 | 23:51:33 |
|  1 | 2016-06-01 | 2016-06-02 | 18:40:33 |
|  2 | 2016-06-02 | 2016-06-02 | 18:40:33 |
|  2 | 2016-06-02 | 2016-06-03 | 11:45:33 |
|  3 | 2016-06-03 | 2016-06-03 | 11:45:33 |
|  3 | 2016-06-03 | 2016-06-04 | 20:01:33 |
|  4 | 2016-06-04 | 2016-06-04 | 20:01:33 |
|  4 | 2016-06-04 | 2016-06-05 | 06:24:33 |

I am using this query

SELECT * FROM tb_workday
LEFT JOIN tb_time ON TBegDate IN (WDDate,DATE_ADD(WDDate,INTERVAL 1 DAY))

but EXPLAIN result of tb_time is 183 rows, which does not utilize the index at all.

Now I changed it to the exact date

SELECT * FROM tb_wd
LEFT JOIN tb_t ON TBegDate IN ("2016-01-01","2016-01-02")

This time the EXPLAIN result is 2 rows.

I wanted the former query to use the index. Please answer this without turning in into a UNION query. I asked this question before (How to make multiple LEFT JOINs with OR fully use a composite index? (part 2)) but because of my English I failed to express my problem I am having, so I made it more simple here. I want to use both the IN clause and the index.

the issue is if you use any mysql function within join statement "ON TBegDate IN (WDDate,DATE_ADD(WDDate,INTERVAL 1 DAY))" it will not utilize index — Mahesh Madushanka, Jun 30 '16 at 08:12
You could store your dates in numeric (epoch seconds) format, which would then allow you to do something like `TBegDate BETWEEN WDDate AND WDDate + (60*60*24)`. — Tim Biegeleisen, Jun 30 '16 at 08:21
@MaheshMadushanka `ON TBegDate IN (DATE_ADD(INTERVAL 1 DAY))` This one uses the index. — Red Romanov, Jun 30 '16 at 08:22
@TimBiegeleisen As far as I observed BETWEEN never uses index. And your suggestion does not work. — Red Romanov, Jun 30 '16 at 08:33
@RedRomanov Yes, my suggestion will work, because a numeric column is sargable, whereas your date column is not. I doubt you even tried my suggestion. The criteria for an index working has nothing to do with `BETWEEN`, while just gets expanded to two `WHERE` conditions, but rather whether the index works on a function. — Tim Biegeleisen, Jun 30 '16 at 08:35
@TimBiegeleisen Yes I tried your suggestion. I created new column, put unix_timestamp date into it, changed the index to it, and changed the query to utilize the numeric date. It doesn't work. Here's the query `SELECT * FROM tb_workday LEFT JOIN tb_time ON tbegdatenum between wddatenum and wddatenum+(60*60*24)` — Red Romanov, Jun 30 '16 at 08:39
Try using a pure numeric type column, where simple addition should work. — Tim Biegeleisen, Jun 30 '16 at 08:41
@TimBiegeleisen Index doesn't always work with comparison/between. http://stackoverflow.com/questions/4691799/why-does-mysql-not-use-an-index-for-a-greater-than-comparison — Red Romanov, Jun 30 '16 at 08:56
@TimBiegeleisen what is wrong with half of your answer you deleted. Tweak it. Or should I say, stop after your first sentence. :p — Drew, Jun 30 '16 at 14:13
@Drew I assume that MySQL `Date` and `DateTime` are generally not index-friendly, so I suggested seconds since epoch but apparently even that didn't work :-( — Tim Biegeleisen, Jun 30 '16 at 14:20
My idea is ditch the calc of `x*y*z` part. Just stop after the first sentence. Let the chips fall where they will. See my answer last nite to Red. — Drew, Jun 30 '16 at 14:21
Here was that [answer](http://stackoverflow.com/a/38113835) . Red is a great guy that tests stuff fast. — Drew, Jun 30 '16 at 14:32

IN clause not using index (part 2)

0 Answers0