查询优化| Magento | MariaDB

Question

请帮助优化以下查询。"Order By“子句不适用于添加的索引。

查询：

SELECT
    `lpsi`.`listing_product_id`
FROM
    `m2epro_listing_product_instruction` AS `lpsi`
LEFT JOIN `m2epro_processing_lock` AS `pl` ON
    pl.object_id = lpsi.listing_product_id
    AND model_name = 'M2ePro/Listing_Product'
WHERE
    (lpsi.component = 'ebay')
    AND (pl.id IS NULL)
GROUP BY
    `lpsi`.`listing_product_id`
ORDER BY
    MAX(lpsi.priority) DESC,
    MIN(lpsi.create_date) ASC
LIMIT 10

索引信息：

m2epro_listing_product_instruction,0,PRIMARY,1,id,A,34,,,"",BTREE,"",""
m2epro_listing_product_instruction,1,listing_product_id,1,listing_product_id,A,34,,,"",BTREE,"",""
m2epro_listing_product_instruction,1,component,1,component,A,4,,,YES,BTREE,"",""
m2epro_listing_product_instruction,1,type,1,type,A,11,,,"",BTREE,"",""
m2epro_listing_product_instruction,1,priority,1,priority,A,8,,,"",BTREE,"",""
m2epro_listing_product_instruction,1,create_date,1,create_date,A,34,,,YES,BTREE,"",""

使用ORDER BY子句解释结果：

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,SIMPLE,lpsi,ALL,component,,,,34,Using where; Using temporary; Using filesort
1,SIMPLE,pl,ref,"model_name,object_id",model_name,"767",const,1,Using where; Not exists

不带ORDER BY子句的解释结果：

CLAUSE:"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,SIMPLE,lpsi,index,component,listing_product_id,"4",,10,Using where
1,SIMPLE,pl,ref,"model_name,object_id",model_name,"767",const,1,Using where; Not exists

提供的表详细信息如下: Show create tables：

CREATE TABLE `m2epro_listing_product_instruction` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`listing_product_id` INT(11) UNSIGNED NOT NULL,
`component` VARCHAR(10) DEFAULT NULL,
`type` VARCHAR(255) NOT NULL,
`initiator` VARCHAR(255) NOT NULL,
`priority` INT(11) UNSIGNED NOT NULL,
`additional_data` LONGTEXT DEFAULT NULL,
`create_date` DATETIME DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `listing_product_id` (`listing_product_id`),
INDEX `component` (`component`),
INDEX `type` (`type`),
INDEX `priority` (`priority`),
INDEX `create_date` (`create_date`)
)

CREATE TABLE `m2epro_processing_lock` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `processing_id` INT(11) UNSIGNED NOT NULL,
  `model_name` VARCHAR(255) NOT NULL,
  `object_id` INT(11) UNSIGNED NOT NULL,
  `tag` VARCHAR(255) DEFAULT NULL,
  `update_date` DATETIME DEFAULT NULL,
  `create_date` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `processing_id` (`processing_id`),
  INDEX `model_name` (`model_name`),
  INDEX `object_id` (`object_id`),
  INDEX `tag` (`tag`)
)

Answer 1

这种复合、覆盖、索引可以加快ORDER BY版本的速度：

INDEX(component, listing_product_id, priority, create_date)

并且，对于另一个表：

INDEX(object_id, model_name, id)  -- in either order

(来自评论)

添加索引后仍未找到优化结果。请检查解释结果。

"id","select_type","table","type","possible_keys", 
   "key","key_len","ref","rows","Extra"  
1,SIMPLE,lpsi,ref,"component,m2epro_listing_produ_idx_component_id",
   m2epro_listing_produ_idx_component_id,"33",const,37,Using where; Using index; Using temporary; Using filesort 
1,SIMPLE,pl,ref,"model_name,object_id",
   model_name,"767",const,1,Using where; Not exists