Mysql 查询优化,将 CASE Exists (SELECT...) 移至左连接

发布于 2024-10-27 19:56:18 字数 3759 浏览 3 评论 0原文

更新 这是 CASE 语句使用的两个表的 ddl

CREATE TABLE product_option (
merchant_id                 smallint unsigned NOT NULL,
product_option_id           smallint unsigned NOT NULL AUTO_INCREMENT,
product_option_name         varchar(255) NOT NULL DEFAULT '',
product_id                  int unsigned NOT NULL DEFAULT 0,    /* Option may be associated with a product  */
package_id                  int unsigned NOT NULL DEFAULT 0,    /* or all products with this package    */
PRIMARY KEY pk_product_option (merchant_id,product_option_id),

) TYPE=MyISAM PACK_KEYS=1 COMMENT='Options';

CREATE TABLE package_2_product (
merchant_id     smallint unsigned NOT NULL,
package_id      int unsigned NOT NULL,
product_id      int unsigned NOT NULL,

PRIMARY KEY pk_package_2_product (merchant_id,package_id,product_id)

) TYPE=MyISAM PACK_KEYS=1 COMMENT='Link product to package';

我有一个查询...

SELECT SQL_CALC_FOUND_ROWS 
  p.*,
  CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg, 
  CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
  CASE WHEN EXISTS (
    SELECT product_option_id 
      FROM product_option
     WHERE merchant_id = 116 AND product_id = p.product_id
     UNION
    SELECT product_id FROM package_2_product
     WHERE merchant_id = 116 AND product_id = p.product_id
  ) THEN 1 ELSE 0 END AS options_exist,
  i.thumbnail,i.thumbnail_width,i.thumbnail_height,
  i.title AS thumbnail_title, i.alt AS thumbnail_alt
FROM 
  product p 
  INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                       AND p.product_id=c2p.product_id 
                                       AND c2p.category_id = 84
  LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                       AND p.product_id = i.product_id
                                       AND i.is_default = 1 
WHERE 
  p.merchant_id = 116
  AND FIND_IN_SET('live',p.param) > 0
  AND FIND_IN_SET('wholesale-only',p.param) = 0
ORDER BY
  p.rank, p.product_name
LIMIT 0, 50;

相关部分是 CASE WHEN EXISTS... 导致查询需要几秒钟才能运行。如果没有子选择,它会在十分之一秒内完成。

我想知道是否有一种方法可以移动这种类型的情况,子选择到内部联接以获得相同的数据,而不是为返回的每一行运行子选择。

尝试以下查询并调整 Coalesce 行和 LEFT JOIN (SELECT...) p1

我收到此错误...

错误代码:1054 “on 子句”中的未知列“p1.product_id”

这是查询...

SELECT SQL_CALC_FOUND_ROWS 
  p.*,
  CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg,  
  CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
  COALESCE(p1.product_option_id, p2.product_id) IS NOT NULL AS options_exist,
  i.thumbnail,i.thumbnail_width,i.thumbnail_height,
  i.title AS thumbnail_title, i.alt AS thumbnail_alt
FROM 
  product p 
  INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                       AND p.product_id=c2p.product_id 
                                       AND c2p.category_id = 84
  LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                       AND p.product_id = i.product_id
                                       AND i.is_default = 1 
  LEFT JOIN (
   SELECT product_option_id
     FROM product_option
    WHERE merchant_id = 116
    GROUP BY product_id) p1 on p1.product_id = p.product_id
   LEFT JOIN (
    SELECT product_id
    FROM package_2_product
   WHERE merchant_id = 116
    GROUP BY product_id) p2 on p1.product_id is null and p2.product_id = p.product_id 
WHERE 
  p.merchant_id = 116
  AND FIND_IN_SET('live',p.param) > 0
  AND FIND_IN_SET('wholesale-only',p.param) = 0
ORDER BY
  p.rank, p.product_name

Update
Here is the ddl of the two tables that the CASE statement uses

CREATE TABLE product_option (
merchant_id                 smallint unsigned NOT NULL,
product_option_id           smallint unsigned NOT NULL AUTO_INCREMENT,
product_option_name         varchar(255) NOT NULL DEFAULT '',
product_id                  int unsigned NOT NULL DEFAULT 0,    /* Option may be associated with a product  */
package_id                  int unsigned NOT NULL DEFAULT 0,    /* or all products with this package    */
PRIMARY KEY pk_product_option (merchant_id,product_option_id),

) TYPE=MyISAM PACK_KEYS=1 COMMENT='Options';

CREATE TABLE package_2_product (
merchant_id     smallint unsigned NOT NULL,
package_id      int unsigned NOT NULL,
product_id      int unsigned NOT NULL,

PRIMARY KEY pk_package_2_product (merchant_id,package_id,product_id)

) TYPE=MyISAM PACK_KEYS=1 COMMENT='Link product to package';

I have a query...

SELECT SQL_CALC_FOUND_ROWS 
  p.*,
  CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg, 
  CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
  CASE WHEN EXISTS (
    SELECT product_option_id 
      FROM product_option
     WHERE merchant_id = 116 AND product_id = p.product_id
     UNION
    SELECT product_id FROM package_2_product
     WHERE merchant_id = 116 AND product_id = p.product_id
  ) THEN 1 ELSE 0 END AS options_exist,
  i.thumbnail,i.thumbnail_width,i.thumbnail_height,
  i.title AS thumbnail_title, i.alt AS thumbnail_alt
FROM 
  product p 
  INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                       AND p.product_id=c2p.product_id 
                                       AND c2p.category_id = 84
  LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                       AND p.product_id = i.product_id
                                       AND i.is_default = 1 
WHERE 
  p.merchant_id = 116
  AND FIND_IN_SET('live',p.param) > 0
  AND FIND_IN_SET('wholesale-only',p.param) = 0
ORDER BY
  p.rank, p.product_name
LIMIT 0, 50;

The pertinent part being the CASE WHEN EXISTS...
is causing the query to take multiple seconds to run. Without the sub-select it finishes in a tenth of a second.

I'm wondering if there is a way to move this type of case, sub-select into an inner join to get the same data, and not have the sub-select run for every row that gets returned.

Attempting the below query with a tweak to the Coalesce line and the LEFT JOIN (SELECT...) p1

I received this error...

Error Code: 1054
Unknown column 'p1.product_id' in 'on clause'

Here is the query...

SELECT SQL_CALC_FOUND_ROWS 
  p.*,
  CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg,  
  CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
  COALESCE(p1.product_option_id, p2.product_id) IS NOT NULL AS options_exist,
  i.thumbnail,i.thumbnail_width,i.thumbnail_height,
  i.title AS thumbnail_title, i.alt AS thumbnail_alt
FROM 
  product p 
  INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                       AND p.product_id=c2p.product_id 
                                       AND c2p.category_id = 84
  LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                       AND p.product_id = i.product_id
                                       AND i.is_default = 1 
  LEFT JOIN (
   SELECT product_option_id
     FROM product_option
    WHERE merchant_id = 116
    GROUP BY product_id) p1 on p1.product_id = p.product_id
   LEFT JOIN (
    SELECT product_id
    FROM package_2_product
   WHERE merchant_id = 116
    GROUP BY product_id) p2 on p1.product_id is null and p2.product_id = p.product_id 
WHERE 
  p.merchant_id = 116
  AND FIND_IN_SET('live',p.param) > 0
  AND FIND_IN_SET('wholesale-only',p.param) = 0
ORDER BY
  p.rank, p.product_name

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

走过海棠暮 2024-11-03 19:56:18
SELECT SQL_CALC_FOUND_ROWS 
  p.*,
  CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg, 
  CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
  COALESCE(p1.product_id, p2.product_id) IS NOT NULL AS options_exist,
  i.thumbnail,i.thumbnail_width,i.thumbnail_height,
  i.title AS thumbnail_title, i.alt AS thumbnail_alt
FROM 
  product p 
  INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                       AND p.product_id=c2p.product_id 
                                       AND c2p.category_id = 84
  LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                       AND p.product_id = i.product_id
                                       AND i.is_default = 1 
  LEFT JOIN (
    SELECT product_id
      FROM product_option
     WHERE merchant_id = 116
     GROUP BY product_id) p1 on p1.product_id = p.product_id
  LEFT JOIN (
    SELECT product_id
      FROM package_2_product
     WHERE merchant_id = 116
     GROUP BY product_id) p2 on p1.product_id is null and p2.product_id = p.product_id

WHERE 
  p.merchant_id = 116
  AND FIND_IN_SET('live',p.param) > 0
  AND FIND_IN_SET('wholesale-only',p.param) = 0
ORDER BY
  p.rank, p.product_name

注意:

  • on p1.product_id is null 添加到第二个左连接,以防止当 p1 已经有结果时执行它。
  • group by 子句防止 LEFT JOIN 通过笛卡尔积扩展结果集

This DDL will create a table with enough fields to show the query working without errors.

create table product_option(merchant_id int, product_id int);
create table package_2_product(merchant_id int, product_id int);
create table category_2_product(merchant_id int, product_id int, category_id int);
create table product_image(merchant_id int, product_id int, is_default int,
    thumbnail int, thumbnail_width int, thumbnail_height int, title int, alt int);
create table product(merchant_id int, product_id int, in_stock_msg int,
    out_stock_msg int, param int, rank int, product_name int);
SELECT SQL_CALC_FOUND_ROWS 
  p.*,
  CASE p.in_stock_msg  WHEN '' THEN 'In stock' ELSE p.in_stock_msg  END AS in_stock_msg, 
  CASE p.out_stock_msg WHEN '' THEN ''         ELSE p.out_stock_msg END AS out_stock_msg,
  COALESCE(p1.product_id, p2.product_id) IS NOT NULL AS options_exist,
  i.thumbnail,i.thumbnail_width,i.thumbnail_height,
  i.title AS thumbnail_title, i.alt AS thumbnail_alt
FROM 
  product p 
  INNER JOIN category_2_product c2p ON p.merchant_id=c2p.merchant_id 
                                       AND p.product_id=c2p.product_id 
                                       AND c2p.category_id = 84
  LEFT  JOIN product_image        i ON p.merchant_id = i.merchant_id
                                       AND p.product_id = i.product_id
                                       AND i.is_default = 1 
  LEFT JOIN (
    SELECT product_id
      FROM product_option
     WHERE merchant_id = 116
     GROUP BY product_id) p1 on p1.product_id = p.product_id
  LEFT JOIN (
    SELECT product_id
      FROM package_2_product
     WHERE merchant_id = 116
     GROUP BY product_id) p2 on p1.product_id is null and p2.product_id = p.product_id

WHERE 
  p.merchant_id = 116
  AND FIND_IN_SET('live',p.param) > 0
  AND FIND_IN_SET('wholesale-only',p.param) = 0
ORDER BY
  p.rank, p.product_name

Notes:

  • on p1.product_id is null is added to the 2nd left join to prevent it being executed when p1 already has a result.
  • The group by clauses prevent the LEFT JOINs from expanding the result set via cartesian product


This DDL will create a table with enough fields to show the query working without errors.

create table product_option(merchant_id int, product_id int);
create table package_2_product(merchant_id int, product_id int);
create table category_2_product(merchant_id int, product_id int, category_id int);
create table product_image(merchant_id int, product_id int, is_default int,
    thumbnail int, thumbnail_width int, thumbnail_height int, title int, alt int);
create table product(merchant_id int, product_id int, in_stock_msg int,
    out_stock_msg int, param int, rank int, product_name int);
凹づ凸ル 2024-11-03 19:56:18

试试这个:

SELECT SQL_CALC_FOUND_ROWS p.*, 
                           CASE p.in_stock_msg 
                             WHEN '' THEN 'In stock' 
                             ELSE p.in_stock_msg 
                           END     AS in_stock_msg, 
                           CASE p.out_stock_msg 
                             WHEN '' THEN '' 
                             ELSE p.out_stock_msg 
                           END     AS out_stock_msg, 
                           CASE 
                             WHEN Isnull(po.product_option_id) 
                                  AND Isnull(p2p.product_id) THEN 0 
                             ELSE 1 
                           END     AS options_exist, 
                           i.thumbnail, 
                           i.thumbnail_width, 
                           i.thumbnail_height, 
                           i.title AS thumbnail_title, 
                           i.alt   AS thumbnail_alt 
FROM   product p 
       INNER JOIN category_2_product c2p 
         ON p.merchant_id = c2p.merchant_id 
            AND p.product_id = c2p.product_id 
            AND c2p.category_id = 84 
       LEFT JOIN product_image i 
         ON p.merchant_id = i.merchant_id 
            AND p.product_id = i.product_id 
            AND i.is_default = 1 
       LEFT JOIN product_option po 
         ON po.merchant_id = 116 
            AND po.product_id = p.product_id 
       LEFT JOIN package_2_product p2p 
         ON p2p.merchant_id = 116 
            AND p2p.product_id = p.product_id 
WHERE  p.merchant_id = 116 
       AND Find_in_set('live', p.param) > 0 
       AND Find_in_set('wholesale-only', p.param) = 0 
GROUP  BY p.prduct_id 
ORDER  BY p.rank, 
          p.product_name 
LIMIT  0, 50; 

您需要在两个表上建立索引

  • (merchant_id,product_id)

Try this one:

SELECT SQL_CALC_FOUND_ROWS p.*, 
                           CASE p.in_stock_msg 
                             WHEN '' THEN 'In stock' 
                             ELSE p.in_stock_msg 
                           END     AS in_stock_msg, 
                           CASE p.out_stock_msg 
                             WHEN '' THEN '' 
                             ELSE p.out_stock_msg 
                           END     AS out_stock_msg, 
                           CASE 
                             WHEN Isnull(po.product_option_id) 
                                  AND Isnull(p2p.product_id) THEN 0 
                             ELSE 1 
                           END     AS options_exist, 
                           i.thumbnail, 
                           i.thumbnail_width, 
                           i.thumbnail_height, 
                           i.title AS thumbnail_title, 
                           i.alt   AS thumbnail_alt 
FROM   product p 
       INNER JOIN category_2_product c2p 
         ON p.merchant_id = c2p.merchant_id 
            AND p.product_id = c2p.product_id 
            AND c2p.category_id = 84 
       LEFT JOIN product_image i 
         ON p.merchant_id = i.merchant_id 
            AND p.product_id = i.product_id 
            AND i.is_default = 1 
       LEFT JOIN product_option po 
         ON po.merchant_id = 116 
            AND po.product_id = p.product_id 
       LEFT JOIN package_2_product p2p 
         ON p2p.merchant_id = 116 
            AND p2p.product_id = p.product_id 
WHERE  p.merchant_id = 116 
       AND Find_in_set('live', p.param) > 0 
       AND Find_in_set('wholesale-only', p.param) = 0 
GROUP  BY p.prduct_id 
ORDER  BY p.rank, 
          p.product_name 
LIMIT  0, 50; 

you will need index on both tables for

  • (merchant_id,product_id)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文