0

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.

References:

  1. Join on "poor" table
  2. Join query with different column types in "on" part

BD: MySQL 5.5.43-0+deb8u1-log

  • The "problem of bonus" You can resolve without changing application - just add one more column DATETIME if need install trigger which will always put summary information. It reduce speed of some information, but give opportunity to use index on this columns. But second problem more serious - any function stop using index (at least for now). Possible solution depend from business task - what is it (reason of query), what size of data, what acceptable time and etc. Some time - using temp tables very good solution for hard queries. – a_vlad Nov 24 '15 at 15:29
  • I'm trying the PROCEDURE / trigger approach, seems right for our scenario, thanks @a_vlad – Gonzalo Vasquez Nov 24 '15 at 17:33
1

Let's jump "out of the box"...

Have a column in the first table with the datetime rounded to the minute. (One minute being your resolution.)

Have two columns in the second table, both datetime, one rounded up to the next minute, the other down.

Index both columns in second table: (id, lower) and (id, upper).

Now run a UNION ALL of two SELECTs, one JOINing the first table to the second via id and lower, with equality; the other via id and upper.

This could catch pairs that are almost 2 minutes apart, so in the outer part of the UNION, do a WHERE to filter using the to-the-second columns -- checking which ones match within one minute.

This technique is likely to be much faster than any other.

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.