所以我有两个表,products_used
大约600MB, products_language_description
大约5MB,但问题是这个查询永远不会完成运行…
我试过修理,优化分析,我不知道如何改善这个…
SELECT pu.products_id, count(pu.products_id) as products_count, p.products_name,
pu.time_used FROM products_used pu, products_language_description p
WHERE pu.merchant_id='69'
AND p.products_id=pu.products_id GROUP BY products_id ORDER BY products_count
DESC LIMIT 0, 20
CREATE TABLE `products_used` (
`products_used_id` INT(15) NOT NULL AUTO_INCREMENT,
`plans_key` VARCHAR(255) NOT NULL DEFAULT '0',
`products_id` BIGINT(20) NOT NULL DEFAULT '0',
`customers_id` INT(10) NOT NULL DEFAULT '0',
`merchant_id` INT(10) NOT NULL DEFAULT '0',
`time_used` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`products_used_id`),
INDEX `plans_key` (`plans_key`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
AUTO_INCREMENT=24625441;
CREATE TABLE `products_language_description` (
`products_id` INT(5) NOT NULL DEFAULT '0',
`products_description` LONGTEXT NOT NULL,
`products_name` TEXT NOT NULL COLLATE 'utf8_general_ci',
`products_help_info` LONGBLOB NOT NULL,
`products_language` VARCHAR(255) NOT NULL DEFAULT '',
PRIMARY KEY (`products_id`, `products_language`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM;
尝试添加索引到product_id
&products_used
表中的merchant_id
字段
看看你的WHERE节。
,pu.merchant_id = ' 69 '和p.products_id =聚氨酯。products_id GROUP BY products_id
你正在比较两种不同的数据类型:
-
products_id
INT (5) NOT NULL默认"0", -
products_id
bigint (20) not null default '0',
还有,你用了不必要的单引号:
- pu.merchant_id = "69"
也许你需要在第一种情况下创建一些索引。外键也有帮助!
也许这对你有帮助!
对于这个查询:
SELECT pu.products_id, count(pu.products_id) as products_count,
p.products_name, pu.time_used
FROM products_used pu join
products_language_description p
on p.products_id = pu.products_id
WHERE pu.merchant_id = '69'
GROUP BY pu.products_id
ORDER BY products_count DESC
LIMIT 0, 20
你想在products_used(merchant_id, products_id)
上建立索引。在MySQL中,有时您可以使用关联子查询重写聚合查询以改进结果:
SELECT pu.products_id,
(select count(*)
from products_language_description p
where p.products_id = pu.products_id
) as products_count,
p.products_name, pu.time_used
FROM products_used pu
WHERE pu.merchant_id = '69'
ORDER BY products_count DESC
LIMIT 0, 20;
这将外部group by
替换为相关查询中的一个聚合,应该只使用主键索引。
等。你的问题是这两个定义:
CREATE TABLE `products_used` (
. . .
`products_id` BIGINT(20) NOT NULL DEFAULT '0',
)
. . .
CREATE TABLE `products_language_description` (
`products_id` INT(5) NOT NULL DEFAULT '0',
. . .
)
连接条件使用不同的数据类型。修复表结构,使列具有相同的类型(使用alter table . . .
)或通过重建它们
正如Gordon所提到的,我会将你的产品使用表的索引扩展到(merchant_id, product_id, time_used),所以它是一个覆盖索引,而不必去原始数据来获得你的count()。现在,对于给定的商家,在products_used表中有多个相同的"products_id"实例,这对我来说似乎很奇怪,但这是另一回事。
我会做产品ID的内部预查询,计数和时间具体到你想要的商人。否则,就像Gordon的查询示例一样,我首先预先查询每个产品,然后再为商家获取这些产品。
我建议预先查询商家特定使用的内部产品,然后一旦返回,获取产品名称。
SELECT
JustByMerchant.products_id,
JustByMerchant.products_count,
p.products_name,
JustByMerchant.time_used
FROM
( select
pu.products_id,
count(*) as products_count,
pu.time_used
from
products_used pu
where
pu.merchant_id = 69
group by
pu.products_id
order by
COUNT(*) DESC
limit
0, 20 ) JustByMerchant
JOIN products_language_description p
ON JustByMerchant.products_id = p.products_id