所以我不得不使用表,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;发布于 2014-03-20 22:44:25
尝试向products_used表上的product_id和merchant_id字段添加索引
发布于 2014-03-20 22:47:20
对于此查询:
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 . . .)或重新构建它们。
发布于 2014-03-20 22:48:48
看看你的WHERE部分。
其中pu.merchant_id='69‘和按products_id分组的p.products_id=pu.products_id
您正在比较两种不同的数据类型:
products_id INT(5) NOT NULL DEFAULT '0',products_id BIGINT(20) NOT NULL DEFAULT '0',此外,您还以不必要的方式使用了单引号:
也许你需要在第一种情况下创建一些指标。外键也很有用!
也许这对你有帮助!
https://stackoverflow.com/questions/22536339
复制相似问题