Re: MySQL subquery
Thanks a lot for the reply.
If I convert the 1st query into a view as following,
mysql> CREATE VIEW myView AS SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS tsmark,
-> r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, r.queue_id AS queue,
-> (c.periodic_time) AS periodic_time,
-> (c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) AS octets_forwarded
-> FROM (service_combined_network_ingress_octets r JOIN service_combined_network_ingress_octets_periodic c ON ((r.id = c.id)))
-> WHERE ISNULL(r.suspect) OR r.suspect = FALSE
-> ;
the query to get the result set is as following,
mysql> SELECT DATE_FORMAT(tsmark, '%Y-%m-01') AS tsmark2, site, lag, port, queue,
-> FLOOR(SUM(periodic_time)) AS periodic_time,
-> SUM(octets_forwarded) AS octets_forwarded
-> FROM myView as t
-> WHERE tsmark BETWEEN str_to_date('2016-09-01 00:00:00', '%Y-%m-%d %H:%i:%s') AND str_to_date('2016-09-01 01:00:00', '%Y-%m-%d %H:%i:%s')
-> AND site IN ('10.1.1.105') AND port='1/1/1' AND queue = 1
-> GROUP BY tsmark2, site, lag, queue
-> ;
Will the where clause in the query be able to merged to the query of the view to reduce the data size for the table join?
The underline two tables of the view contains 30m+ records each, my query targets only a very small portion of the records;
the majority of the usecases is less than 1%.