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?

  • You are experiencing one reason not to split a DATETIME into two fields! – Rick James Dec 2 '15 at 18:49

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).

  • Thanks, I'll try that approach, as I was just using plain CONCAT, with a space in between both fields, but didn't seem like the best way to do it – Gonzalo Vasquez Nov 24 '15 at 13:55
  • The VIRTUAL column sounds pretty interesting, sounds like mixing a VIEW into the TABLE – Gonzalo Vasquez Nov 24 '15 at 13:56
  • your edit on using indexes might trim down the rowcount for the join, I'll see what happens with that too – Gonzalo Vasquez Nov 24 '15 at 13:58
  • Yeah, which one to use will probably matter if you have some WHERE clause that restricts the number of rows from one of the two tables. – ypercubeᵀᴹ Nov 24 '15 at 14:01
  • If you use CONCAT, you are essentially converting the columns from both tables to varchar. No index would be effectively used that way. The query will have to do either 2 full table scans or 2 full index scans. – ypercubeᵀᴹ Nov 24 '15 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.