Re: Optimize - Using Temp, Using Filesort, Group By Having

[Lenny Dunn]

Here are the tables. You will notice a BLOB for signature image storage. I have since re-thought this and will be moving the image out to the file system. The table only has 30,000 records and is already 155MB+. When it hits a million records, the size will not be worth the trouble.

CREATE TABLE  `mobilepod_db`.`dlv_transaction_activity_tb` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `tracking_number` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `device_username` varchar(255) NOT NULL DEFAULT 'N/A',
  `customer_id` int(10) NOT NULL DEFAULT '0',
  `status_code` varchar(255) NOT NULL,
  `notes` tinytext,
  `content` tinytext,
  `signature_text` varchar(255) DEFAULT NULL,
  `signature_image` blob,
  PRIMARY KEY (`id`),
  KEY `tracking_number` (`tracking_number`),
  KEY `device_username` (`device_username`),
  KEY `customer_id` (`customer_id`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=29511 DEFAULT CHARSET=utf8;
CREATE TABLE  `mobilepod_db`.`dlv_transaction_tb` (
  `tracking_number` varchar(255) NOT NULL,
  `create_date` datetime NOT NULL,
  `ready_time` datetime DEFAULT NULL,
  `due_time` datetime DEFAULT NULL,
  `customer_id` int(10) NOT NULL DEFAULT '0',
  `request_type` varchar(1) NOT NULL DEFAULT 'D',
  `orig_name` varchar(255) DEFAULT NULL,
  `orig_address1` tinytext,
  `orig_address2` tinytext,
  `orig_city` tinytext,
  `orig_state` tinytext,
  `orig_zip` tinytext,
  `orig_pickup_location` tinytext,
  `orig_contact` varchar(255) DEFAULT NULL,
  `orig_phone` tinytext,
  `orig_email` tinytext,
  `dest_name` varchar(255) DEFAULT NULL,
  `dest_address1` tinytext,
  `dest_address2` tinytext,
  `dest_city` tinytext,
  `dest_state` tinytext,
  `dest_zip` tinytext,
  `dest_drop_location` tinytext,
  `dest_contact` varchar(255) DEFAULT NULL,
  `dest_phone` tinytext,
  `dest_email` tinytext,
  `notes` tinytext,
  `refrigerate` varchar(1) DEFAULT 'N',
  `weight` varchar(255) DEFAULT NULL,
  `customer_generated` varchar(1) NOT NULL DEFAULT 'N',
  `complete` varchar(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (`tracking_number`),
  KEY `ready_time` (`ready_time`),
  KEY `due_time` (`due_time`),
  KEY `customer_id` (`customer_id`),
  KEY `complete` (`complete`),
  KEY `create_date` (`create_date`),
  KEY `complete_due_time` (`complete`,`due_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Here are the table status.

Name				Engine 	Version Row_format	Rows 	Avg_row_length 	Data_length 	Max_data_length Index_length 	Data_free 	Auto_increment 	Create_time 		Update_time 	Check_time 	Collation 	Checksum
dlv_transaction_activity_tb 	InnoDB 	10 	Compact 	16029 	9393 		150568960 	0 		5275648 	52428800 	29528 		2010-05-12 12:50:48 	NULL 		NULL 		utf8_general_ci NULL 	  	 
dlv_transaction_tb 		InnoDB 	10 	Compact 	7050 	522 		3686400 	0 		9535488 	52428800 	NULL 		2010-05-12 11:28:39 	NULL 		NULL 		utf8_general_ci NULL

This morning, the query took 0.0054 seconds and returned 35 records. This database is still young but will eventually grow to the millions of records. This is the reason why I wanted to make sure the query was ready for the big time. Here is the explain from the query this morning as well as the buffer information.

id	select_type 	table 			type 	possible_keys 					key 			key_len 	ref 						rows 	Extra
1 	SIMPLE 		dlv_transaction_tb 	range 	PRIMARY,due_time,complete,complete_due_time 	complete_due_time 	14 		NULL 						35 	Using where
1 	SIMPLE 		t1 			ref 	tracking_number 				tracking_number 	767 		mobilepod_db.dlv_transaction_tb.tracking_number 5 	 
1 	SIMPLE 		t2 			ref 	tracking_number,date 				tracking_number 	767 		mobilepod_db.t1.tracking_number 		5 	Using where; Not exists
1 	SIMPLE 		dlv_status_tb 		eq_ref 	PRIMARY 					PRIMARY 		767 		mobilepod_db.t1.status_code 			1 	 
1 	SIMPLE 		dlv_status_type_tb 	eq_ref 	PRIMARY 					PRIMARY 		767 		mobilepod_db.dlv_status_tb.type_code 		1 	 
1 	SIMPLE 		device_tb 		eq_ref 	PRIMARY 					PRIMARY 		767 		mobilepod_db.t1.device_username 		1 	 
1 	SIMPLE 		customer_tb 		eq_ref 	PRIMARY 					PRIMARY 		4 		mobilepod_db.dlv_transaction_tb.customer_id 	1 	 

Variable_name 		Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 112197632
innodb_log_buffer_size 	2097152
join_buffer_size 	131072
key_buffer_size 	57671680
myisam_sort_buffer_size 72351744
net_buffer_length 	16384
preload_buffer_size 	32768
read_buffer_size 	65536
read_rnd_buffer_size 	262144
sort_buffer_size 	262144
sql_buffer_result 	OFF

Thanks again for your assistance.