2

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?

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

1 Answer 1

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

5
  • 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 Nov 24, 2015 at 13:55
  • The VIRTUAL column sounds pretty interesting, sounds like mixing a VIEW into the TABLE Nov 24, 2015 at 13:56
  • your edit on using indexes might trim down the rowcount for the join, I'll see what happens with that too Nov 24, 2015 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. Nov 24, 2015 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. Nov 24, 2015 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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