想学习改进慢速mysql查询

I have a MySQL query to select all product id's with certain filters applied to the products. This query works but I want to learn to improve this query. Alternatives for this query are welcome with explanation.

我有一个MySQL查询来选择所有产品ID，并将某些过滤器应用于产品。此查询有效，但我想学习改进此查询。欢迎使用此查询的替代方案进行说明。

SELECT kkx_products.id from kkx_products WHERE display = 'yes' AND id in
    (SELECT product_id FROM `kkx_filters_products` WHERE `filter_id` in 
       (SELECT id FROM `kkx_filters` WHERE kkx_filters.urlname = "comics" OR kkx_filters.urlname = "comicsgraphicnovels") 
    group by product_id having count(*) = 2) 
    ORDER BY kkx_products.id desc LIMIT 0, 24

I've included the structure of the tables being used in the query.

我已经包含了查询中使用的表的结构。

EXPLAINkkx_filters;

EXPLAINkkx_filters;

Field            Type                 Null  Key     Default        Extra
id               int(11) unsigned     NO    PRI     NULL           auto_increment
name             varchar(50)          NO             
filtergroup_id   int(11)              YES   MUL     NULL     
urlname          varchar(50)          NO    MUL     NULL     
date_modified    timestamp            NO            CURRENT_TIMESTAMP    
orderid          float(11,2)          NO            NULL

EXPLAIN kkx_filters_products;

EXPLAIN kkx_filters_products;

Field            Type                 Null  Key   Default          Extra
filter_id        int(11)              NO    PRI   0    
product_id       int(11)              NO    PRI   0

EXPLAIN kkx_products;

EXPLAIN kkx_products;

Field            Type                 Null  Key   Default          Extra
id               int(11)              NO    PRI   NULL             auto_increment
title            varchar(255)         NO           
urlname          varchar(50)          NO    MUL        
description      longtext             NO          NULL   
price            float(11,2)          NO          NULL   
orderid          float(11,2)          NO          NULL   
imageurl         varchar(255)         NO            
date_created     datetime             NO          NULL    
date_modified    timestamp            NO          CURRENT_TIMESTAMP    
created_by       varchar(11)          NO          NULL    
modified_by      varchar(11)          NO          NULL   
productnumber    varchar(32)          NO           
instock          enum('yes','no')     NO          yes    
display          enum('yes','no')     NO          yes

1 个解决方案

#1

Instead of using inline queries in your criteria statements, try using the EXISTS block... http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

不要在条件语句中使用内联查询，请尝试使用EXISTS块... http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

You will be able to see the difference in your explain plan. Before you had a query executing for each and every record in your result set, and every result in that inline view result set had its own query executing to.

您将能够看到解释计划中的差异。在对结果集中的每个记录执行查询之前，该内联视图结果集中的每个结果都有自己的查询执行。

You see how nested inline views can create an exponential increase in cost. EXISTS doesn't work that way.

您将看到嵌套内联视图如何创建成本的指数增长。 EXISTS无法正常工作。

Example of the use of EXISTS:

使用EXISTS的示例：

Consider tbl1 has columns id and data. tbl2 has columns id, parentid, and data.

考虑tbl1有列id和数据。 tbl2具有列id，parentid和数据。

SELECT a.*
FROM tbl1 a
WHERE 1 = 1 
AND EXISTS (
  SELECT NULL 
  FROM tbl2 b
  WHERE b.parentid = a.id 
  AND b.data = 'SOME CONDITIONAL DATA TO CONSTRAIN ON'
)

1) We can assume the 1 = 1 is some condition that equates to true for every record 2) Doesn't matter what we select in the EXISTS statment really, NULL is fine. 3) It is important to look at b.parentid = a.id, this links our exist statement to the result set

1）我们可以假设1 = 1是某些条件，对于每条记录都等于为真2）无论我们在EXISTS语句中选择的是什么，NULL都没关系。 3）重要的是看b.parentid = a.id，这将我们的exists语句链接到结果集

#1