Use Indexes For Join on Indexed DATETIME and Indexed DATE columns

January 24, 2023

I have three tables a, b, and c in my MySQL 5.7 database. SHOW CREATE statements for each table are:

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `DT` date DEFAULT NULL,
  `USER` int(11) DEFAULT NULL,
  `COMMENT_SENTIMENT` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a_DT_USER_IDX` (`DT`,`USER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `DT` datetime DEFAULT NULL,
  `USER` int(11) DEFAULT NULL,
  `COMMENT_SENTIMENT` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b_DT_USER_IDX` (`DT`,`USER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `c` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `DT` date DEFAULT NULL,
  `USER` int(11) DEFAULT NULL,
  `COMMENT_SENTIMENT` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b_DT_USER_IDX` (`DT`,`USER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Table a has a DATE column a.DT, table b has a DATETIME column b.DT, and table c has a DATE column c.DT.

All of these DT columns are indexed.

As a caveat, while b.DT is a DATETIME, all of the ‘time’ portions in it are 00:00:00 and they always will be. It probably should be a DATE, but I cannot change it.

I want to join table a and table b on their DT columns, but explain tells me that their indices are not used:

Cannot use ref access on index 'b.DT_datetime_index' due to type or collation conversion on field 'DT'

When I join table a and b on a.DT and b.DT

SELECT *
FROM a
JOIN b
ON a.DT = b.DT;

The result is much slower than when I do the same with a and c

SELECT *
FROM a
JOIN c
ON a.DT = c.DT;

Is there a way to use the indices in join from the first query on a.DT = b.DT, specifically without altering the tables? I’m not sure if b.DT having only 00:00:00 for the time portion could be relevant in a solution.

The end goal is a faster select using this join.

Thank you!

— What I’ve done section —

I compared the joins between a.DT = b.DT and a.DT = c.DT, and saw the time difference.
I also tried wrapping b‘s DT column with DATE(b.DT), but explain gave the same issue, which is pretty expected.

>Solution :

MySQL won’t use an index to join DATE and DATETIME columns.

You can create a virtual column with the corresponding DATE and use that.

CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `DT` datetime DEFAULT NULL,
  `USER` int(11) DEFAULT NULL,
  `COMMENT_SENTIMENT` int(11) DEFAULT NULL,
  `DT_DATE` DATE AS (DATE(DT)),
  PRIMARY KEY (`id`),
  KEY `b_DT_USER_IDX` (`DT_DATE`,`USER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT *
FROM a
JOIN b
ON a.DT = b.DT_DATE;