i have following query:
select count(inspection_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , inspection_result= 1 , part_type = "39000048" , location = "1" union select count(inspection_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , inspection_result= 0 , part_type = "39000048" , location = "1" union select count(defect_1_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_1_result= 0 , part_type = "39000048" , location = "1" union select count(defect_2_value_1_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_2_value_1_result= 0 , part_type = "39000048" , location = "1" union select count(defect_2_value_2_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_2_value_2_result= 0 , part_type = "39000048" , location = "1" union select count(defect_3_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_3_result= 0 , part_type = "39000048" , location = "1" union select count(defect_4_value_1_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_4_value_1_result= 0 , part_type = "39000048" , location = "1" union select count(defect_4_value_2_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_4_value_2_result= 0 , part_type = "39000048" , location = "1" union select count(defect_5_result) copygtron (inspection_time between "2015-07-15" , "2015-07-21") , defect_5_result= 0 , part_type = "39000048" , location = "1"... i have execute query 3, 4 , 5.. or 10 months or weeks according user input. there according 1.1 million data , incoming data 72 000 per hour , executing query takes around 6 seconds, not . has reduced 20 millisecond. partitioning has been done 12 months , inspection_time , defects columns reduced.
can me?
explain sql. find out whats slow, add indexes , or optimize queries. worst case scenario may need either make summary table or shard tables
Comments
Post a Comment