rendered paste body
mysql> explain SELECT COUNT(*) FROM `cart_sales_flat_quote_shipping_rate` WHERE `cart_sales_flat_quote_shipping_rate`.`address_id` IN ( SELECT `address_id` FROM `cart_sales_flat_quote_address` WHERE `cart_sales_flat_quote_address`.`quote_id` IN ( SELECT `entity_id` FROM `cart_sales_flat_quote` WHERE `cart_sales_flat_quote`.`reserved_order_id` IN ( SELECT `increment_id` FROM `cart_sales_order` WHERE `updated_at` < @increment_date ) ) );
+----+--------------------+-------------------------------------+-----------------+------------------+--------------------------------------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------------------------------+-----------------+------------------+--------------------------------------+---------+------+--------+--------------------------+
| 1 | PRIMARY | cart_sales_flat_quote_shipping_rate | index | NULL | FK_SALES_QUOTE_SHIPPING_RATE_ADDRESS | 4 | NULL | 495098 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | cart_sales_flat_quote_address | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | cart_sales_flat_quote | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | cart_sales_order | index_subquery | IDX_INCREMENT_ID | IDX_INCREMENT_ID | 152 | func | 1 | Using where |
+----+--------------------+-------------------------------------+-----------------+------------------+--------------------------------------+---------+------+--------+--------------------------+
4 rows in set (0.00 sec)
mysql> explain SELECT * FROM `cart_sales_flat_quote_shipping_rate` WHERE `cart_sales_flat_quote_shipping_rate`.`address_id` IN ( SELECT `address_id` FROM `cart_sales_flat_quote_address` WHERE `cart_sales_flat_quote_address`.`quote_id` IN ( SELECT `entity_id` FROM `cart_sales_flat_quote` WHERE `cart_sales_flat_quote`.`reserved_order_id` IN ( SELECT `increment_id` FROM `cart_sales_order` WHERE `updated_at` < @increment_date ) ) );
+----+--------------------+-------------------------------------+-----------------+------------------+------------------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------------------------------+-----------------+------------------+------------------+---------+------+--------+-------------+
| 1 | PRIMARY | cart_sales_flat_quote_shipping_rate | ALL | NULL | NULL | NULL | NULL | 495098 | Using where |
| 2 | DEPENDENT SUBQUERY | cart_sales_flat_quote_address | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | cart_sales_flat_quote | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | cart_sales_order | index_subquery | IDX_INCREMENT_ID | IDX_INCREMENT_ID | 152 | func | 1 | Using where |
+----+--------------------+-------------------------------------+-----------------+------------------+------------------+---------+------+--------+-------------+
4 rows in set (0.00 sec)
cart_sales_flat_quote_shipping_rate | CREATE TABLE `cart_sales_flat_quote_shipping_rate` (
`rate_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`address_id` int(10) unsigned NOT NULL DEFAULT '0',
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`carrier` varchar(255) DEFAULT NULL,
`carrier_title` varchar(255) DEFAULT NULL,
`code` varchar(255) DEFAULT NULL,
`method` varchar(255) DEFAULT NULL,
`method_description` text,
`price` decimal(12,4) NOT NULL DEFAULT '0.0000',
`error_message` text,
`method_title` text,
PRIMARY KEY (`rate_id`),
KEY `FK_SALES_QUOTE_SHIPPING_RATE_ADDRESS` (`address_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1775089 DEFAULT CHARSET=utf8 PACK_KEYS=0 ROW_FORMAT=COMPACT |