Search code examples
mysqlsqlperformanceindexingquery-performance

Improve SQL Query perofrmance

I have a complex query which takes 700ms to run on my machine. I found that the bottleneck is the ORDER BY at_firstname.value clause, but how can I use indexes to improve this?

SELECT 
    `e`.*
    , `at_default_billing`.`value` AS `default_billing`
    , `at_billing_postcode`.`value` AS `billing_postcode`
    , `at_billing_city`.`value` AS `billing_city`
    , `at_billing_region`.`value` AS `billing_region`
    , `at_billing_country_id`.`value` AS `billing_country_id`
    , `at_company`.`value` AS `company`
    , `at_firstname`.`value` AS `firstname`
    , `at_lastname`.`value` AS `lastname`
    , CONCAT(at_firstname.value
    , " "
    , at_lastname.value) AS `full_name`
    , `at_phone`.`value` AS `phone`
    , IFNULL(at_phone.value,"N/A") AS `telephone`
    , `e`.`entity_id` AS `id` 
FROM 
    `customer_entity` AS `e`  
LEFT JOIN 
    `customer_entity_int` AS `at_default_billing` 
    ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) 
    AND (`at_default_billing`.`attribute_id` = '13')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_postcode` 
    ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`)        
    AND (`at_billing_postcode`.`attribute_id` = '30')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_city` 
    ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_billing_city`.`attribute_id` = '26')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_region` 
    ON (`at_billing_region`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_billing_region`.`attribute_id` = '28')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_country_id` 
    ON (`at_billing_country_id`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_billing_country_id`.`attribute_id` = '27')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_company` 
    ON (`at_company`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_company`.`attribute_id` = '24')  
LEFT JOIN 
    `customer_entity_varchar` AS `at_firstname` 
    ON (`at_firstname`.`entity_id` = `e`.`entity_id`) 
    AND (`at_firstname`.`attribute_id` = '5')  
LEFT JOIN 
    `customer_entity_varchar` AS `at_lastname` 
    ON (`at_lastname`.`entity_id` = `e`.`entity_id`) 
    AND (`at_lastname`.`attribute_id` = '7')  
LEFT JOIN 
    `customer_entity_varchar` AS `at_phone` 
    ON (`at_phone`.`entity_id` = `e`.`entity_id`) 
    AND (`at_phone`.`attribute_id` = '136')  
ORDER BY 
    `at_firstname`.`value` ASC LIMIT 20

This is execution plan : enter image description here

EXPLAIN of Query :

'1','SIMPLE','e',NULL,'ALL',NULL,NULL,NULL,NULL,'19951','100.00','Using temporary; Using filesort'
'1','SIMPLE','at_default_billing',NULL,'eq_ref','UNQ_CUSTOMER_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_INT_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_INT_ENTITY_ID,IDX_CUSTOMER_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.e.entity_id,const','1','100.00',NULL
'1','SIMPLE','at_billing_postcode',NULL,'eq_ref','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.at_default_billing.value,const','1','100.00','Using where'
'1','SIMPLE','at_billing_city',NULL,'eq_ref','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.at_default_billing.value,const','1','100.00','Using where'
'1','SIMPLE','at_billing_region',NULL,'eq_ref','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.at_default_billing.value,const','1','100.00','Using where'
'1','SIMPLE','at_billing_country_id',NULL,'eq_ref','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.at_default_billing.value,const','1','100.00','Using where'
'1','SIMPLE','at_company',NULL,'eq_ref','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ADDRESS_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.at_default_billing.value,const','1','100.00','Using where'
'1','SIMPLE','at_firstname',NULL,'eq_ref','UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.e.entity_id,const','1','100.00',NULL
'1','SIMPLE','at_lastname',NULL,'eq_ref','UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.e.entity_id,const','1','100.00',NULL
'1','SIMPLE','at_phone',NULL,'eq_ref','UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID,IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE','UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID','6','lazurd.e.entity_id,const','1','100.00',NULL

Table Structure:

CREATE TABLE `customer_entity_varchar` (
  `value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value Id',
  `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
  `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Id',
  `entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Id',
  `value` varchar(255) DEFAULT NULL COMMENT 'Value',
  PRIMARY KEY (`value_id`),
  UNIQUE KEY `UNQ_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID` (`entity_id`,`attribute_id`),
  KEY `IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_TYPE_ID` (`entity_type_id`),
  KEY `IDX_CUSTOMER_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
  KEY `IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID` (`entity_id`),
  KEY `IDX_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_VALUE` (`entity_id`,`attribute_id`,`value`),
  CONSTRAINT `FK_CSTR_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CSTR_ENTT_VCHR_ENTT_TYPE_ID_EAV_ENTT_TYPE_ENTT_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CUSTOMER_ENTITY_VARCHAR_ENTITY_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`entity_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=131094 DEFAULT CHARSET=utf8 COMMENT='Customer Entity Varchar';
Solution

  • As of now your query is:

    1. Performing ALL left outer joins first.
    2. Then ORDERing the rows.
    3. Then LIMITing the rows.

    I would perform the strictly needed outer joins first, then ordering and limiting (to reduce to 20 rows), and finally I would do all the rest of the outer joins. In short I would do:

    1. Performing minimal left outer join first. That is, two tables only.
    2. Then ORDERing the rows.
    3. Then LIMITing the rows. This produce a max of 20 rows.
    4. Perform all the rest of outer joins. At this point this is not thousands of rows anymore, but only 20.

    This change should massively reduce the "Unique Key Lookup" executions. The modified query will look like:

    select
  e.*
  , `at_default_billing`.`value` AS `default_billing`
  , `at_billing_postcode`.`value` AS `billing_postcode`
  , `at_billing_city`.`value` AS `billing_city`
  , `at_billing_region`.`value` AS `billing_region`
  , `at_billing_country_id`.`value` AS `billing_country_id`
  , `at_company`.`value` AS `company`
  , `at_lastname`.`value` AS `lastname`
  , CONCAT(firstname
  , " "
  , at_lastname.value) AS `full_name`
  , `at_phone`.`value` AS `phone`
  , IFNULL(at_phone.value,"N/A") AS `telephone`
from ( -- Step #1: joining customer_entity with customer_entity_varchar
SELECT 
    `e`.*
    , `at_firstname`.`value` AS `firstname`
    , `e`.`entity_id` AS `id` 
FROM 
    `customer_entity` AS `e`  
LEFT JOIN 
    `customer_entity_varchar` AS `at_firstname` 
    ON (`at_firstname`.`entity_id` = `e`.`entity_id`) 
    AND (`at_firstname`.`attribute_id` = '5')  
ORDER BY -- Step #2: Sorting (the bare minimum)
    `at_firstname`.`value` ASC 
LIMIT 20 -- Step #3: Limiting (to 20 rows)
) e
LEFT JOIN -- Step #4: Performing all the rest of outer joins (only few rows now)
    `customer_entity_int` AS `at_default_billing` 
    ON (`at_default_billing`.`entity_id` = `e`.`entity_id`) 
    AND (`at_default_billing`.`attribute_id` = '13')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_postcode` 
    ON (`at_billing_postcode`.`entity_id` = `at_default_billing`.`value`)        
    AND (`at_billing_postcode`.`attribute_id` = '30')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_city` 
    ON (`at_billing_city`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_billing_city`.`attribute_id` = '26')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_region` 
    ON (`at_billing_region`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_billing_region`.`attribute_id` = '28')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_billing_country_id` 
    ON (`at_billing_country_id`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_billing_country_id`.`attribute_id` = '27')  
LEFT JOIN 
    `customer_address_entity_varchar` AS `at_company` 
    ON (`at_company`.`entity_id` = `at_default_billing`.`value`) 
    AND (`at_company`.`attribute_id` = '24')  
LEFT JOIN 
    `customer_entity_varchar` AS `at_lastname` 
    ON (`at_lastname`.`entity_id` = `e`.`entity_id`) 
    AND (`at_lastname`.`attribute_id` = '7')  
LEFT JOIN 
    `customer_entity_varchar` AS `at_phone` 
    ON (`at_phone`.`entity_id` = `e`.`entity_id`) 
    AND (`at_phone`.`attribute_id` = '136')