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字段。

#2

Take a look at you WHERE section.

看一看你在哪里。

WHERE pu.merchant_id='69' AND p.products_id=pu.products_id GROUP BY products_id

聚氨酯。merchant_id = ' 69 '和p.products_id =聚氨酯。products_id GROUP BY products_id

You are comparing two different data types:

您正在比较两种不同的数据类型:

products_id INT(5) NOT NULL DEFAULT '0',
products_id INT(5)不为空默认值为0，
products_id BIGINT(20) NOT NULL DEFAULT '0',
products_id BIGINT(20) NOT NULL DEFAULT '0'，

Also, you are using single quotes in an unnecesary way:

同样，你也用一种不需要的方式使用单引号:

pu.merchant_id='69'
pu.merchant_id = ' 69 '

Maybe you need to create some indeces in the first case. A foreign key helps too!

也许您需要在第一个案例中创建一些indeces。外键也有帮助!

Maybe this helps you!

也许这能帮助你!

#3

For this query:

对于这个查询:

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

You want an index on products_used(merchant_id, products_id). In MySQL, sometimes you can rewrite an aggregation query using a correlated subquery to improve results:

您需要一个products_used(products_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;

This replaces the outer group by with an aggregation in the correlated query that should just used the primary key index.

这将替换外部组，并在相关查询中进行聚合，而这些查询应该只使用主键索引。

EDIT:

编辑:

Wait. Your problem are these two definitions:

等待。你的问题是这两个定义:

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',
     . . . 
)

The join conditions use a different data type. Fix the table structures so the columns have the same type (using alter table . . .) or by rebuilding them.

连接条件使用不同的数据类型。修复表结构，使列具有相同的类型(使用alter table . .)或通过重新构建它们。

#4

As Gordon mentioned, I would have an extend the index on your products used table to (merchant_id, product_id, time_used ) so it is a covering index and does not have to go to the raw data to get your count(). Now, it appear weird to me that you would have multiple instances of the SAME "products_id" in the products_used table for a given merchant, but that's another thing.

正如Gordon所提到的，我将对您的产品使用表的索引进行扩展(salesant_id、product_id、time_used)，因此它是一个覆盖索引，不需要使用原始数据来获得您的count()。现在，我觉得奇怪的是，在products_used表中有多个相同的“products_id”实例，但这是另一回事。

I would do an internal prequery of the product ID, count and time SPECIFIC TO THE MERCHANT you want. Otherwise, as in Gordon's query sample, I was prequerying EVERY Product first, then outside of that getting those for the merchant.

我将对您想要的商家进行产品ID、计数和时间的内部预查询。否则，就像在Gordon的查询示例中一样，我先预先查询每个产品，然后在此之外为商家获取这些产品。

I am proposing prequery the internal products used specific to the merchant, then once THAT is returned, get the product name.

我的建议是预先查询特定于商家的内部产品，然后再返回，获取产品名称。

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

#1