I have two tables with a one-to-many relationship.
Table1
ID name email
Table2
ID table1_ID date
I need to get all the data from Table1 where
:
MAX(date) from Table2 < "2016-01-01"
This doesn't work. Max is considered as "invalid" in where clause. What I did was :
SELECT Table1.name, Table1.email, tmp.maxdate
FROM Table1
JOIN ( SELECT MAX(date) maxdate, table1_ID
FROM Table2
GROUP BY table1_ID ) as tmp
ON tmp.table1_ID = table1.id
WHERE tmp.maxdate < "2016-01-01"
AND (other conditions)
So this works. BUT I think the performance is going to be awful - explain
shows that all the Table2
is being read, and this table will grow a lot.
Any idea on how I could do it otherwise, or how to improve my current query performances ?
Try:
SELECT Table1.name, Table1.email, tmp.maxdate
FROM Table1
INNER JOIN ( SELECT MAX(date) maxdate, table1_ID
FROM Table2
GROUP BY table1_ID
HAVING maxdate > "2016-01-01" ) as tmp
ON tmp.table1_ID = table1.id
WHERE
AND (other conditions)
Before, you just bringing back everyone from Table2 and join it with Table1. This will knock off all those without the maxdate > "2016-01-01" and do join on it with Table1.