mysql - 通过表进行多个联接的查询优化

Question

我需要为所有产品建立不同的记录，因为现在大多数产品都有多个关键字，因此会记录多个记录。这是一个 mysql 查询，需要针对搜索进行优化，因为它相当慢。有什么想法吗？我很感谢您的帮助

SELECT DISTINCT 
  `p`.`slug`,
  `p`.`image_code`,
  `p`.`name` AS `products_name`,
  `a`.`name` AS `authors_name`,
  `c`.`name` AS `category_name`,
  `cf`.`name` AS `color_family_name` 
FROM
  `products` AS `p` 
  INNER JOIN
  `categories_products` AS `cp` 
  ON (`p`.`id` = `cp`.`product_id`) 
  INNER JOIN
  `categories` AS `c` 
  ON (`cp`.`categories_id` = `c`.`id`) 
  INNER JOIN
  `artists_products` AS `ap` 
  ON (`p`.`id` = `ap`.`product_id`) 
  INNER JOIN
  `artists` AS `a` 
  ON (`ap`.`artists_id` = `a`.`id`) 
  INNER JOIN
  `products_keywords` AS `pk` 
  ON (`p`.`id` = `pk`.`product_id`) 
  INNER JOIN
  `keywords` AS `kw` 
  ON (`pk`.`keyword_id` = `kw`.`id`) 
  INNER JOIN
  `colors_products` AS `cop` 
  ON (`p`.`id` = `cop`.`product_id`) 
  INNER JOIN
  `colors` AS `col` 
  ON (`cop`.`colors_id` = `col`.`id`) 
  INNER JOIN
  `colors_family` AS `cf` 
  ON (
    `col`.`color_family_id` = `cf`.`id`
  ) 
WHERE `p`.`image_code` LIKE '%red%' 
  OR `p`.`slug` LIKE '%red%' 
  OR `p`.`name` LIKE '%red%' 
  OR `a`.`name` LIKE '%red%' 
  OR `c`.`name` LIKE '%red%' 
  OR `kw`.`name` LIKE '%red%' 
  OR `col`.`name` LIKE '%red%' 
  OR `cf`.`name` LIKE '%red%' 
  OR `col`.`pantone_code` LIKE '%red%' 
  AND `p`.`active` = 1 
LIMIT 60 

Answer 1

不幸的是，由于您在查询中使用了大量的 OR 条件，因此性能只会如此好。优化的主要领域是在连接之前限制结果集并将不需要的连接移动到 WHERE 子句。这将允许 MySQL 优化其使用的数据以加快查询速度。由于您没有从关键字表返回数据，因此这是合乎逻辑的起点。此外，您可以尽早削减产品表以丢弃不活动的记录。

SELECT DISTINCT 
  `p`.`slug`,
  `p`.`image_code`,
  `p`.`name` AS `products_name`,
  `a`.`name` AS `authors_name`,
  `c`.`name` AS `category_name`,
  `cf`.`name` AS `color_family_name` 
FROM
  (SELECT `p`.`id`, `p`.`slug`, `p`.`image_code`, `p`.`name` FROM `products` AS `p` WHERE `p`.`active` = 1) AS `p`
INNER JOIN `artists_products` AS `ap` ON (`p`.`id` = `ap`.`product_id`) 
INNER JOIN `artists` AS `a` ON (`ap`.`artists_id` = `a`.`id`) 

INNER JOIN `categories_products` AS `cp` ON (`p`.`id` = `cp`.`product_id`) 
INNER JOIN `categories` AS `c` ON (`cp`.`categories_id` = `c`.`id`) 

INNER JOIN `colors_products` AS `cop` ON (`p`.`id` = `cop`.`product_id`) 
INNER JOIN `colors` AS `col` ON (`cop`.`colors_id` = `col`.`id`) 
INNER JOIN `colors_family` AS `cf` ON (`col`.`color_family_id` = `cf`.`id`) 

WHERE `p`.`image_code` LIKE '%red%' 
  OR `p`.`slug` LIKE '%red%' 
  OR `p`.`name` LIKE '%red%' 
  OR `a`.`name` LIKE '%red%' 
  OR `c`.`name` LIKE '%red%' 
  OR `p`.`id`. IN (SELECT `pk`.`product_id` FROM `products_keywords` INNER JOIN `keywords` AS `kw` ON (`pk`.`keyword_id` = `kw`.`id`) WHERE `kw`.`name` LIKE '%red%')
  OR `col`.`name` LIKE '%red%' 
  OR `cf`.`name` LIKE '%red%' 
  OR `col`.`pantone_code` LIKE '%red%'
LIMIT 60