Preciso fazer um OUTER JOIN em duas tabelas, baseado em 2 colunas, primeiro id um INTEGER id, que é bem simples de usar na consulta, mas o segundo é um DATETIME, que na verdade não corresponde a um valor na tabela outra tabela, mas precisa "combinar" a data mais próxima.
Como um bônus adicional ao problema, na tabela de referência, os dados não são como DATETIME, mas em duas colunas separadas: uma DATE e outra TIME.
Essa pergunta é derivada de outras duas que já fiz antes, mas como preciso restringir os problemas, preferi fazer perguntas separadas.
A questão é como escolher a data mais próxima em uma abordagem de melhor desempenho. Eu tentei usar entre (tolerância de 1 minuto) e coisas assim, mas a consulta demora muito e o plano de explicação fornece muitas linhas nas tabelas envolvidas.
Talvez algo como usar subconsultas possa funcionar mais rápido, mas simplesmente não sei como criá-los para a sintaxe JOIN.
Referências:
BD: MySQL 5.5.43-0+deb8u1-log
Vamos pular "fora da caixa"...
Tenha uma coluna na primeira tabela com o datetime arredondado para o minuto. (Um minuto sendo sua resolução.)
Tenha duas colunas na segunda tabela, ambas datetime, uma arredondada para o próximo minuto e a outra para baixo .
Indexe ambas as colunas na segunda tabela:
(id, lower)e(id, upper).Agora execute um
UNION ALLde doisSELECTs, umJOINingda primeira tabela para a segunda via id e inferior, com igualdade; o outro via id e superior.Isso pode capturar pares com quase 2 minutos de intervalo, portanto, na parte externa do
UNION, faça umWHEREpara filtrar usando as colunas até o segundo - verificando quais correspondem em um minuto.Essa técnica provavelmente será muito mais rápida do que qualquer outra.