join은 위험한 동물이다(?)

데이터가 좀 지랄 같이(?) 많은 녀석이긴 한데

mysql> select count(*) from dht11; +----------+ | count(*) | +----------+ |    57186 | +----------+ 1 row in set (0.09 sec) mysql> select count(*) from ph; +----------+ | count(*) | +----------+ |    57637 | +----------+ 1 row in set (0.17 sec)

아무생각 없이 조인하고 조건식을 돌리니 8초.. ㄷㄷ

select * from dht11 as T,ph as P where T.created = P.created and DATE(T.created) >= '2018-12-20';

97 rows in set (8.12 sec)

날짜 쳐내고 조인하니 0.44초!

select * from  (select * from dht11 where DATE(created) >= '2018-12-20' ) as T, (select * from ph where DATE(created) >= '2018-12-20' ) as P  WHERE T.created = P.created;

98 rows in set (0.44 sec)

+

요건 DATE() 찾아 본다고 링크 추가

[링크 : https://code.i-harness.com/ko-kr/q/1c4e6a]