Sandro Antonucci Sandro Antonucci - 6 months ago 23
SQL Question

Select from a sub select using the same table optimization

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
from vas_updates
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;


I bet it takes so much time because the query has
from temp, vas_updates
, is there a way to isolate the "last query"?

Answer

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.