Best Index Strategy

[Devrishi Shandilya]

Hi Friends,

What is the best index strategy for these two queries for maximum parformance as row examined is huge. please find explain plan as well, please suggest....

Is composite index on soas.(telephone,address_type).

Query 1:

SELECT `main_table`.*, `payment`.`method`, `soas`.`telephone`, `soas`.`email`, `soas`.`postcode`, `soas`.`region` FROM `sales_flat_order_grid` AS `main_table`
INNER JOIN `sales_flat_order_payment` AS `payment` ON main_table.entity_id=parent_id INNER JOIN `sales_flat_order_address` AS `soas`
ON soas.parent_id=main_table.entity_id and soas.address_type = "billing" WHERE (telephone LIKE '%7600639533%') ORDER BY created_at DESC LIMIT 20;

Query 2:

SELECT COUNT(*) FROM `sales_flat_order_grid` AS `main_table` INNER JOIN `sales_flat_order_payment` AS `payment` ON main_table.entity_id=parent_id
INNER JOIN `sales_flat_order_address` AS `soas` ON soas.parent_id=main_table.entity_id and soas.address_type = "billing" WHERE (telephone LIKE '%7766095413%');


Query 1 explain plan :

mysql> explain SELECT `main_table`.*, `payment`.`method`, `soas`.`telephone`, `soas`.`email`, `soas`.`postcode`, `soas`.`region` FROM `sales_flat_order_grid` AS `main_table` INNER JOIN `sales_flat_order_payment` AS `payment` ON main_table.entity_id=parent_id INNER JOIN `sales_flat_order_address` AS `soas` ON soas.parent_id=main_table.entity_id and soas.address_type = "billing" WHERE (telephone LIKE '%7600639533%') ORDER BY created_at DESC LIMIT 20;
+----+-------------+------------+-------+----------------------------------------+----------------------------------------+---------+------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+----------------------------------------+----------------------------------------+---------+------------------------------+------+-------------+
| 1 | SIMPLE | main_table | index | PRIMARY | IDX_SALES_FLAT_ORDER_GRID_CREATED_AT | 5 | NULL | 20 | NULL |
| 1 | SIMPLE | payment | ref | IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID | IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID | 4 | magento.main_table.entity_id | 1 | NULL |
| 1 | SIMPLE | soas | ref | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID | 5 | magento.main_table.entity_id | 1 | Using where |



Query 2 explain plan :

mysql> explain SELECT COUNT(*) FROM `sales_flat_order_grid` AS `main_table` INNER JOIN `sales_flat_order_payment` AS `payment` ON main_table.entity_id=parent_id
-> INNER JOIN `sales_flat_order_address` AS `soas` ON soas.parent_id=main_table.entity_id and soas.address_type = "billing" WHERE (telephone LIKE '%7766095413%');
+----+-------------+------------+--------+----------------------------------------+----------------------------------------+---------+---------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------------------+----------------------------------------+---------+---------------------------+--------+-------------+
| 1 | SIMPLE | payment | index | IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID | IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID | 4 | NULL | 213690 | Using index |
| 1 | SIMPLE | main_table | eq_ref | PRIMARY | PRIMARY | 4 | magento.payment.parent_id | 1 | Using index |
| 1 | SIMPLE | soas | ref | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID | 5 | magento.payment.parent_id | 1 | Using where |



---------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `sales_flat_order_grid` (
`entity_id` int(10) unsigned NOT NULL COMMENT 'Entity Id',
`status` varchar(32) DEFAULT NULL COMMENT 'Status',
`store_id` smallint(5) unsigned DEFAULT NULL COMMENT 'Store Id',
`store_name` varchar(255) DEFAULT NULL COMMENT 'Store Name',
`purchase_from` varchar(250) DEFAULT 'Main Website' COMMENT 'Purchase from App Or Website',
`customer_id` int(10) unsigned DEFAULT NULL COMMENT 'Customer Id',
`base_grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Base Grand Total',
`base_total_paid` decimal(12,4) DEFAULT NULL COMMENT 'Base Total Paid',
`grand_total` decimal(12,4) DEFAULT NULL COMMENT 'Grand Total',
`total_paid` decimal(12,4) DEFAULT NULL COMMENT 'Total Paid',
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
`base_currency_code` varchar(3) DEFAULT NULL COMMENT 'Base Currency Code',
`order_currency_code` varchar(255) DEFAULT NULL COMMENT 'Order Currency Code',
`shipping_name` varchar(255) DEFAULT NULL COMMENT 'Shipping Name',
`billing_name` varchar(255) DEFAULT NULL COMMENT 'Billing Name',
`created_at` timestamp NULL DEFAULT NULL COMMENT 'Created At',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Updated At',
PRIMARY KEY (`entity_id`),
UNIQUE KEY `UNQ_SALES_FLAT_ORDER_GRID_INCREMENT_ID` (`increment_id`),
KEY `IDX_SALES_FLAT_ORDER_GRID_STATUS` (`status`),
KEY `IDX_SALES_FLAT_ORDER_GRID_STORE_ID` (`store_id`),
KEY `IDX_SALES_FLAT_ORDER_GRID_BASE_GRAND_TOTAL` (`base_grand_total`),
KEY `IDX_SALES_FLAT_ORDER_GRID_BASE_TOTAL_PAID` (`base_total_paid`),
KEY `IDX_SALES_FLAT_ORDER_GRID_GRAND_TOTAL` (`grand_total`),
KEY `IDX_SALES_FLAT_ORDER_GRID_TOTAL_PAID` (`total_paid`),
KEY `IDX_SALES_FLAT_ORDER_GRID_SHIPPING_NAME` (`shipping_name`),
KEY `IDX_SALES_FLAT_ORDER_GRID_BILLING_NAME` (`billing_name`),
KEY `IDX_SALES_FLAT_ORDER_GRID_CREATED_AT` (`created_at`),
KEY `IDX_SALES_FLAT_ORDER_GRID_CUSTOMER_ID` (`customer_id`),
KEY `IDX_SALES_FLAT_ORDER_GRID_UPDATED_AT` (`updated_at`),
CONSTRAINT `FK_SALES_FLAT_ORDER_GRID_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `FK_SALES_FLAT_ORDER_GRID_ENTITY_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`entity_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_SALES_FLAT_ORDER_GRID_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Grid'


----------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `sales_flat_order_payment` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`parent_id` int(10) unsigned NOT NULL COMMENT 'Parent Id',
`base_shipping_captured` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Captured',
`shipping_captured` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Captured',
`amount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Amount Refunded',
`base_amount_paid` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Paid',
`amount_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Amount Canceled',
`base_amount_authorized` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Authorized',
`base_amount_paid_online` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Paid Online',
`base_amount_refunded_online` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Refunded Online',
`base_shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Amount',
`shipping_amount` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Amount',
`amount_paid` decimal(12,4) DEFAULT NULL COMMENT 'Amount Paid',
`amount_authorized` decimal(12,4) DEFAULT NULL COMMENT 'Amount Authorized',
`base_amount_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Ordered',
`base_shipping_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Shipping Refunded',
`shipping_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Shipping Refunded',
`base_amount_refunded` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Refunded',
`amount_ordered` decimal(12,4) DEFAULT NULL COMMENT 'Amount Ordered',
`base_amount_canceled` decimal(12,4) DEFAULT NULL COMMENT 'Base Amount Canceled',
`quote_payment_id` int(11) DEFAULT NULL COMMENT 'Quote Payment Id',
`additional_data` text COMMENT 'Additional Data',
`cc_exp_month` varchar(255) DEFAULT NULL COMMENT 'Cc Exp Month',
`cc_ss_start_year` varchar(255) DEFAULT NULL COMMENT 'Cc Ss Start Year',
`echeck_bank_name` varchar(255) DEFAULT NULL COMMENT 'Echeck Bank Name',
`method` varchar(255) DEFAULT NULL COMMENT 'Method',
`cc_debug_request_body` varchar(255) DEFAULT NULL COMMENT 'Cc Debug Request Body',
`cc_secure_verify` varchar(255) DEFAULT NULL COMMENT 'Cc Secure Verify',
`protection_eligibility` varchar(255) DEFAULT NULL COMMENT 'Protection Eligibility',
`cc_approval` varchar(255) DEFAULT NULL COMMENT 'Cc Approval',
`cc_last4` varchar(255) DEFAULT NULL COMMENT 'Cc Last4',
`cc_status_description` varchar(255) DEFAULT NULL COMMENT 'Cc Status Description',
`echeck_type` varchar(255) DEFAULT NULL COMMENT 'Echeck Type',
`cc_debug_response_serialized` varchar(255) DEFAULT NULL COMMENT 'Cc Debug Response Serialized',
`cc_ss_start_month` varchar(255) DEFAULT NULL COMMENT 'Cc Ss Start Month',
`echeck_account_type` varchar(255) DEFAULT NULL COMMENT 'Echeck Account Type',
`last_trans_id` varchar(255) DEFAULT NULL COMMENT 'Last Trans Id',
`cc_cid_status` varchar(255) DEFAULT NULL COMMENT 'Cc Cid Status',
`cc_owner` varchar(255) DEFAULT NULL COMMENT 'Cc Owner',
`cc_type` varchar(255) DEFAULT NULL COMMENT 'Cc Type',
`po_number` varchar(255) DEFAULT NULL COMMENT 'Po Number',
`cc_exp_year` varchar(255) DEFAULT NULL COMMENT 'Cc Exp Year',
`cc_status` varchar(255) DEFAULT NULL COMMENT 'Cc Status',
`echeck_routing_number` varchar(255) DEFAULT NULL COMMENT 'Echeck Routing Number',
`account_status` varchar(255) DEFAULT NULL COMMENT 'Account Status',
`anet_trans_method` varchar(255) DEFAULT NULL COMMENT 'Anet Trans Method',
`cc_debug_response_body` varchar(255) DEFAULT NULL COMMENT 'Cc Debug Response Body',
`cc_ss_issue` varchar(255) DEFAULT NULL COMMENT 'Cc Ss Issue',
`echeck_account_name` varchar(255) DEFAULT NULL COMMENT 'Echeck Account Name',
`cc_avs_status` varchar(255) DEFAULT NULL COMMENT 'Cc Avs Status',
`cc_number_enc` varchar(255) DEFAULT NULL COMMENT 'Cc Number Enc',
`cc_trans_id` varchar(255) DEFAULT NULL COMMENT 'Cc Trans Id',
`paybox_request_number` varchar(255) DEFAULT NULL COMMENT 'Paybox Request Number',
`address_status` varchar(255) DEFAULT NULL COMMENT 'Address Status',
`additional_information` text COMMENT 'Additional Information',
PRIMARY KEY (`entity_id`),
KEY `IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID` (`parent_id`),
CONSTRAINT `FK_SALES_FLAT_ORDER_PAYMENT_PARENT_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=223015 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Payment'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `sales_flat_order_address` (
`entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity Id',
`parent_id` int(10) unsigned DEFAULT NULL COMMENT 'Parent Id',
`customer_address_id` int(11) DEFAULT NULL COMMENT 'Customer Address Id',
`quote_address_id` int(11) DEFAULT NULL COMMENT 'Quote Address Id',
`region_id` int(11) DEFAULT NULL COMMENT 'Region Id',
`customer_id` int(11) DEFAULT NULL COMMENT 'Customer Id',
`fax` varchar(255) DEFAULT NULL COMMENT 'Fax',
`region` varchar(255) DEFAULT NULL COMMENT 'Region',
`postcode` varchar(255) DEFAULT NULL COMMENT 'Postcode',
`lastname` varchar(255) DEFAULT NULL COMMENT 'Lastname',
`street` varchar(255) DEFAULT NULL COMMENT 'Street',
`city` varchar(255) DEFAULT NULL COMMENT 'City',
`email` varchar(255) DEFAULT NULL COMMENT 'Email',
`telephone` varchar(255) DEFAULT NULL COMMENT 'Telephone',
`country_id` varchar(2) DEFAULT NULL COMMENT 'Country Id',
`firstname` varchar(255) DEFAULT NULL COMMENT 'Firstname',
`address_type` varchar(255) DEFAULT NULL COMMENT 'Address Type',
`prefix` varchar(255) DEFAULT NULL COMMENT 'Prefix',
`middlename` varchar(255) DEFAULT NULL COMMENT 'Middlename',
`suffix` varchar(255) DEFAULT NULL COMMENT 'Suffix',
`company` varchar(255) DEFAULT NULL COMMENT 'Company',
`vat_id` text COMMENT 'Vat Id',
`vat_is_valid` smallint(6) DEFAULT NULL COMMENT 'Vat Is Valid',
`vat_request_id` text COMMENT 'Vat Request Id',
`vat_request_date` text COMMENT 'Vat Request Date',
`vat_request_success` smallint(6) DEFAULT NULL COMMENT 'Vat Request Success',
PRIMARY KEY (`entity_id`),
KEY `IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID` (`parent_id`),
CONSTRAINT `FK_SALES_FLAT_ORDER_ADDRESS_PARENT_ID_SALES_FLAT_ORDER_ENTITY_ID` FOREIGN KEY (`parent_id`) REFERENCES `sales_flat_order` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=446031 DEFAULT CHARSET=utf8 COMMENT='Sales Flat Order Address'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------