Join query with different column types in "on" part

Question

In need to make a JOIN on 2 tables based on columns which have different column types.

On table A, I have a DATETIME field and, on table B, I have a DATE and a TIME field, which combined would match the DATETIME field on table A.

What would be the recommended syntax for best performance on such join clause?

BD: MySQL 5.5.43-0+deb8u1-log

PD: Any extra info needed?

Answer 1

You can use ADDTIME() function:

tableA AS a JOIN tableB AS b
  ON a.datetime_column = ADDTIME(CAST(b.date_column AS DATETIME), b.time_column)

This might use an index on tableA (datetime_column) but not an index on tableB. The reverse might use an index on tableB (date_column, time_column) but not on A:

tableA AS a JOIN tableB AS b
  ON  CAST(a.datetime_column AS DATE) = b.date_column
  AND CAST(a.datetime_column AS TIME) = b.time_column

It won't hurt testing both versions. If one table is much larger than the other, then prefer to have the larger table's columns exposed (not cast) so their index might be used.

---

If you move to MariaDB (any version 5.3+) or MySQL 5.7 (when it's released), you can define a VIRTUAL column (or two) in one of the two tables to hold this conversion/calculation that can be persisted and indexed.

In 5.5, if efficiency is not good, which is expected with large tables, you could add a computed column yourself but it would have to be populated during inserts and kept in sync during updates by you (e.g. using triggers).