Mysql查询需要太多时间来执行。

So I have to tables, products_used aprox 600MB and products_language_description which is about 5MB, but the thing is that this query never finishes running...

因此，我必须使用表，products_used aprox600mb和products_language_description，它大约是5MB，但问题是这个查询从来没有结束运行…

I have tried REPAIR,OPTIMIZE ANALYZE, I'm out of ideas how to improve this...

我尝试过修复，优化分析，我不知道如何改进这个…

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;

4 个解决方案

#1

Try adding index to product_id & merchant_id fields on products_used table

在products_used表中尝试添加索引到product_id和producant_id字段。

4 个解决方案

#1

更多相关文章

随机推荐