-5

I have the following query:

select count(inspection_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and inspection_result= 1 and part_type = "39000048" and location = "1" union all 
select count(inspection_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and inspection_result= 0 and part_type = "39000048" and location = "1" union all select count(defect_1_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_1_result= 0 and part_type = "39000048" and location = "1" union all 
select count(defect_2_value_1_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_2_value_1_result= 0 and part_type = "39000048" and location = "1" union all 
select count(defect_2_value_2_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_2_value_2_result= 0 and part_type = "39000048" and location = "1" union all 
select count(defect_3_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_3_result= 0 and part_type = "39000048" and location = "1" union all 
select count(defect_4_value_1_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_4_value_1_result= 0 and part_type = "39000048" and location = "1" union all 
select count(defect_4_value_2_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_4_value_2_result= 0 and part_type = "39000048" and location = "1" union all 
select count(defect_5_result) from copygtron where (inspection_time between "2015-07-15" and "2015-07-21") and defect_5_result= 0 and part_type = "39000048" and location = "1"...

I have to execute this query for 3, 4 , 5.. or 10 months or weeks according to user input. There are according 1.1 million data and incoming data will be 72 000 per hour and for executing this query it takes around 6 seconds, which is not good . It has to be reduced to 20 millisecond. Partitioning has been done for 12 months and inspection_time and defects columns are reduced.

Can anyone help me?

James Z
  • 12,209
  • 10
  • 24
  • 44
Reshma Chaturvedi
  • 1
  • 1
  • share table structure – Aman Aggarwal Jul 09 '15 at 14:39
  • CREATE TABLE `gtron_component_info` `part_count` bigint(20) DEFAULT NULL, `system_id` varchar(50) DEFAULT NULL,`packaging_level_1_no` varchar(50) DEFAULT NULL,`location` varchar(50) DEFAULT NULL, `part_type` varchar(50) DEFAULT NULL `inspection_time` datetime DEFAULT NULL, `Total_Count` bigint(20) DEFAULT NULL, `inspection_result` tinyint(4) DEFAULT NULL, `defect_1_value_1` float(8,4) DEFAULT NULL, – Reshma Chaturvedi Jul 10 '15 at 05:26
  • `defect_1_result` tinyint(4) DEFAULT NULL, `defect_2_value_1` float(8,4) DEFAULT NULL,\n `defect_2_value_1_result` tinyint(4) DEFAULT NULL,\n `defect_2_value_2` float(8,4) , `defect_2_value_2_result` tinyint(4) ,`defect_2_result` tinyint(4) DEFAULT NULL,\n `defect_3_value_1` float(8,4) ,\n `defect_3_result` tinyint(4) DEFAULT NULL,\n `defect_4_value_1` float(8,4) ,\n `defect_4_value_1_result` tinyint(4) , `defect_4_value_2` float(8,4) ,`defect_4_value_2_result` tinyint(4)DEFAULT `defect_4_result` tinyint(4) DEFAULT NULL,`defect_5_value_1` float(8,4),`defect_5_result` tinyint(4) , – Reshma Chaturvedi Jul 10 '15 at 05:31
  • KEY `defect_1_result` (`defect_1_result`),\n KEY `defect_2_value_1_result` (`defect_2_value_1_result`),\n KEY `defect_2_value_2_result` (`defect_2_value_2_result`),\n KEY `defect_2_result` (`defect_2_result`),\n KEY `defect_3_result` (`defect_3_result`),\n KEY `defect_4_value_1_result` (`defect_4_value_1_result`), KEY `defect_4_value_2_result` (`defect_4_value_2_result`), KEY `defect_4_result` (`defect_4_result`),KEY `defect_5_result` (`defect_5_result`),KEY `inspection_time` (`inspection_time`),\n KEY `inspection_result` (`inspection_result`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 – Reshma Chaturvedi Jul 10 '15 at 05:33
  • PARTITION BY RANGE (to_days(inspection_time)) (PARTITION p1 VALUES LESS THAN ("15-02-01") , PARTITION p2 VALUES LESS THAN ("15-03-01") \n PARTITION p3 VALUES LESS THAN ("15-04-01"),PARTITION p4 VALUES LESS THAN ("15-05-01") , PARTITION p5 VALUES LESS THAN ("5-06-01"),\n PARTITION p6 VALUES LESS THAN ("15-07-01") ,\n PARTITION p7 VALUES LESS THAN ("15-08-01"),\n PARTITION p8 VALUES LESS THAN ("15-09-01") ,\n PARTITION p9 VALUES LESS THAN ("15-10-01") \n PARTITION p10 VALUES LESS THAN ("15-11-01") ENGINE = ,\n PARTITION p11 VALUES LESS THAN MAXVALUE) – Reshma Chaturvedi Jul 10 '15 at 05:37

1 Answers1

1

EXPLAIN your SQL. Find out whats slow, add indexes and or optimize your queries. Worst case scenario you may need to either make a summary table or to shard your tables

lrossy
  • 533
  • 3
  • 10
  • @ReshmaChaturvedi sharding just means to split up your data across multiple tables, usually for write heavy table or for massive datasets. For example, you may want to shard by YYYY-MM, so that each month gets its own table. By doing this, you have a much smaller dataset. Of course, there is a massive cost. I would recommend moving your reporting off mySQL entirely, to something like Elasticsearch which supports sharding, and queries across multiple shards – lrossy Jul 10 '15 at 16:30