select COUNT(p.id) AS `num`, cat.id, cat.name, cat.parent_id AS `parent_id`
from products p
INNER JOIN `products_categories` AS `pc` ON p.id=pc.products_id
INNER JOIN `categories` AS `cat` ON pc.categories_id=cat.id
WHERE p.status = 1 AND p.gender IN ('female','neutral')
group by cat.id
解释查询:
1 SIMPLE p ref PRIMARY,gender,status status 1 const 139107 Using where; Using temporary; Using filesort
1 SIMPLE pc ref products_id,categories products_id 4 mydb.p.id 1 Using index
1 SIMPLE cat eq_ref PRIMARY,categoryname PRIMARY 4 mydb.pc.categories_id 1 Using where
相关指标:
products 0 PRIMARY 1 id A 299339 BTREE
products 1 title 1 title A 299339 BTREE
products 1 sku 1 sku A 299339 BTREE
products 1 body 1 body A 299339 200 BTREE
products 1 short_description 1 short_description A 299339 200 YES BTREE
products 1 keywords 1 keywords A 2 200 BTREE
products 1 gender 1 gender A 10 BTREE
products 1 status 1 status A 2 BTREE
products 1 brand_id 1 brand_id A 3741 YES BTREE
products 1 merchant 1 merchant_id A 52 BTREE
products 1 title_2 1 title,body,keywords 299339 FULLTEXT
products 1 title_3 1 title 299339 FULLTEXT
products 1 body_2 1 body 299339 FULLTEXT
products_categories 0 PRIMARY 1 id A 514054 BTREE
products_categories 1 products_id 1 products_id, categories_id A 514054 BTREE
products_categories 1 categories 1 categories_id A 266 BTREE
categories 0 PRIMARY 1 id A 154 BTREE
categories 1 categoryname 1 name A 154 BTREE
这是一个数据库,其中包含产品,类别以及它们之间的N:N关系。产品可以属于 1 个或多个类别。
我基本上需要一个查询来告诉我,对于我当前的产品过滤器(在这种情况下,状态和性别),该类别是否有任何产品(所以我可以隐藏没有产品的类别)。目前,我统计了每个类别中的产品,以了解这一点。
该查询中的参数将根据用户选择的过滤器而改变,因此该部分在该优化中不是非常重要。
我不需要一个类别的产品的确切数量,只要他们有产品或没有。Products表有相当多的索引,有products_categories和categories表。产品表有大约40万个产品,150个类别和50万个产品_类别。
MySQL 5.6.22 托管在 AWS RDS 上,所有表都在 InnoDB 中。
我明白我的解释查询显示了为什么这很慢(经历了很多产品),但我不知道如何优化它...也许是一种不同的方式来思考这个问题?
您的查询返回139107条匹配记录,因为您使用的筛选条件没有太大限制(status=1、gender=female或neutral)。试试这个
SELECT cat.id, cat.name, cat.parent_id AS `parent_id`,
COUNT(p.id) AS `num`
FROM `categories` AS `cat`
INNER JOIN `products_categories` AS `pc` ON pc.categories_id=cat.id
INNER JOIN products AS p ON p.id=pc.products_id
WHERE p.status = 1 AND p.gender IN ('female','neutral')
GROUP BY cat.id
HAVING COUNT(p.id)>0
添加HAVING
不会自动改进查询。问题是您的过滤条件返回许多匹配的产品。按性别或布尔状态(true/false)过滤记录可能会因为许多重复值而导致表扫描发生,即使状态和性别是索引,MySQL可能仍然认为运行表扫描比使用索引更便宜。
have
用于过滤没有产品的任何类别。试试这个
SELECT cat.id, cat.name, cat.parent_id AS `parent_id`,
COUNT(pc._products_id) AS `num`
FROM `categories` AS `cat`
INNER JOIN `products_categories` AS `pc` ON pc.categories_id=cat.id
GROUP BY cat.id
HAVING COUNT(pc.products_id)>0
上述查询不会与products表联接。它只是查看product_ categories,如果它们有关联的产品。
对于此查询:
select COUNT(p.id) AS `num`, cat.id, cat.name, cat.parent_id AS `parent_id`
from products p INNER JOIN
products_categories `pc`
ON p.id = pc.products_id INNER JOIN
categories cat
ON pc.categories_id = cat.id
WHERE p.status = 1 AND p.gender IN ('female', 'neutral')
group by cat.id;
您需要所有连接
键上的索引。我推荐产品(status、性别、id)
、products_categories(products_id、categories_id)
和类别(id)
。
有时,在MySQL中,使用相关子查询比组
更快:
select c.*,
(select count(*)
from products_categories `pc` INNER JOIN
products p
ON p.id = pc.products_id
where pc.categories_id = cat.id AND
p.status = 1 AND p.gender IN ('female', 'neutral')
) as cnt
from categories c;
这个版本需要产品_类别(categories_id,products_id)
和产品(id,status,gender)
的索引。
问题内容: 我尝试寻找类似的解决方案,但对我来说似乎并没有太大帮助。我有这样一个表: 并且我正在尝试获取针对特定位置的长名称的数量。到目前为止,我所查询的内容也尝试强制重新排序,这就是为什么您看到and的原因。但是,如果某个位置没有人要使用特定标签,则会完全从响应中删除该标签。我希望它返回0。我该怎么做? 问题答案: 也许这个?:
主要内容:概述,一、关联查询优化,1.左(右)外连接,2.内连接,3.JOIN语句原理,4.JOIN小结,5.Hash Join,二、子查询优化,三、排序优化,四、GROUP BY优化,五、优先考虑覆盖索引,六、使用前缀索引,七、索引下推ICP,八、其他查询优化,1.COUNT(*)与COUNT(具体字段)效率,2.不使用SELECT *,3.LIMIT 1优化,4.多使用commit概述 数据库调优的方式有多种: 建立索引、充分利用到索引、不让索引失效 对SQL语句进行优化 调优如缓冲、线程数
我目前正在使用以下代码获取WooCommerce产品类别: 当前显示所有类别,但我希望仅显示子类别。 例如,如果您在“类别1”页面上,则该页面应仅显示该类别中的所有子级。 我在这里看过很多例子,但都找不到适合我需要的东西。
有ci_trail表,字段为:id, uid(用户id), address(地址), create_time 记录人的定位轨迹,此表大概有100w条数据。想查询每个人最新的一条地址信息。使用如下sql: 查询计划如下图: 可见进行了全表扫描,查询效率很低,请问这种情况应该如何优化sql? 已解决 方案1: 方案2: 先将子查询中的id查询出来,然后将id的结果集逗号隔开填充到in中。因为in的内容
我尝试了许多方法,但我无法显示具有类别名称或id的产品
本文向大家介绍MySQL查询按名称分组并在新列中显示计数,包括了MySQL查询按名称分组并在新列中显示计数的使用技巧和注意事项,需要的朋友参考一下 与该方法一起使用GROUP BY 。将名称与GROUP BY分组,然后使用方法进行计数。让我们首先创建一个表- 使用插入命令在表中插入一些记录- 使用select语句显示表中的所有记录- 这将产生以下输出- 以下是按名称分组并显示计数的查询- 这将产生