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
3
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
3
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语句链接到结果集