MySQL Forums
Forum List  »  Optimizer & Parser

Re: MySQL subquery
Posted by: harry sheng
Date: November 06, 2016 08:50PM

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%.

Options: ReplyQuote


Subject
Views
Written By
Posted
907
October 27, 2016 02:51PM
536
October 28, 2016 01:03AM
Re: MySQL subquery
536
November 06, 2016 08:50PM
574
November 07, 2016 01:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.