I need to make an OUTER JOIN on two tables, based on 2 columns, first id an INTEGER id, which is pretty straightforward to use in the query, but the second one is a DATETIME, which doesn't actually matches a value on the other table, but needs to "match" the closest date.
As an added bonus to the problem, on the ref table data is not as DATETIME, but on two separate columns: one DATE and another TIME.
This question is a derivate of other two I've asked before, but as I need to narrow down the problems, I preferred to make separate questions.
The question is how to choose the closest date on a best performance approach. I've tried using between (1 minute tolerance) and things like that, but the query takes too long and the explain plan gives too many rows on the involved tables.
Perhaps something like using subqueries might work faster, but I just don't know how to build them for the JOIN syntax.
BD: MySQL 5.5.43-0+deb8u1-log