how would you optimize this query which currently takes 10s? I need to select ids with the all enties that are filtered inside a subquery with specific conditions.
select temp.ip, vas_updates.setting, vas_updates.version
from (select count(id_update) as counter, ip
where date(date)="2016-05-09" and
time(date) between("14:00:00") and ("23:00:00")
group by ip
having counter =2
) temp, vas_updates
where vas_updates.ip = temp.ip
order by ip;
from temp, vas_updates
I would write this as:
select i.ip, u.setting, u.version from (select count(id_update) as counter, ip from vas_updates where date between '2016-05-09 14:00:00' and '2016-05-09 23:00:00' group by ip having counter = 2 ) i join vas_updates u on u.ip = i.ip order by ip;
Then index on
vas_updates(date, ip) and
vas_updates(ip) should help.