为什么这个查询需要这么长时间？

This query is appearing in my slow log on a mysql system,

这个查询出现在我在mysql系统上的慢速登录中,

# Query_time: 37  Lock_time: 0  Rows_sent: 5  Rows_examined: 405199
select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created,
application_price.retail_price, euroapps.count FROM application_price INNER JOIN
euroapps ON euroapps.id = application_price.application_id WHERE
application_price.storefront_id = '143441' AND application_price.retail_price <= 0
ORDER BY created DESC LIMIT 5;

You see it examines 405,199 rows, could that be the cause of the long query time?

你看它检查405,199行,这可能是查询时间长的原因吗?

A similar query never shows up in my slow log, that query is:

类似的查询永远不会出现在我的慢速日志中,该查询是:

select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created,
application_price.retail_price, euroapps.count FROM application_price INNER JOIN
euroapps ON euroapps.id = application_price.application_id WHERE
application_price.storefront_id = '$store' AND application_price.retail_price > 0
ORDER BY created DESC LIMIT 5

Here is the output of explain:

这是解释的输出:

mysql> explain select euroapps.id, euroapps.name, euroapps.imageurl, euroapps.created, application_price.retail_price, euroapps.count FROM application_price INNER JOIN euroapps ON euroapps.id = application_price.application_id WHERE application_price.storefront_id = '143441' AND application_price.retail_price <= 0 ORDER BY created DESC LIMIT 5;
+----+-------------+-------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table             | type   | possible_keys                    | key                      | key_len | ref                                         | rows   | Extra                                                     |
+----+-------------+-------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | application_price | range  | PRIMARY,idx_storedfront_price_id | idx_storedfront_price_id | 9       | NULL                                        | 110491 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | euroapps          | eq_ref | PRIMARY                          | PRIMARY                  | 4       | itunesapps.application_price.application_id |      1 |                                                           | 
+----+-------------+-------------------+--------+----------------------------------+--------------------------+---------+---------------------------------------------+--------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

3 个解决方案

#1

You ought to look at the execution plan, that will help narrow down the cause. http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

您应该查看执行计划,这将有助于缩小原因。 http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html

#2

Looking at your WHERE clause, you can see that you use application_price.storefront_id as filtering factor. However, in your EXPLAIN, it doesn't appear as possible key meaning it's not indexed - meaning that full table scan is required.

查看WHERE子句,可以看到使用application_price.storefront_id作为过滤因子。但是,在EXPLAIN中,它似乎不是关键意味着它没有被索引 - 这意味着需要全表扫描。

The other factor is application_price.retail_price, you can see what RANGE in explain means - however its cardinality is apparently low - hence so many rows.

另一个因素是application_price.retail_price,你可以看到解释中的RANGE意味着什么 - 但它的基数显然很低 - 因此这么多行。

As Jason Swett suggested - index your application_price.storefront_id and you should see better performance (and Jason you should probably post your comment as an answer).

正如Jason Swett建议的那样 - 索引你的application_price.storefront_id,你应该看到更好的表现(和杰森你应该发表你的评论作为答案)。

#3

The "rows" column of the explain indicates that MySQL eastimates that it will have to examine 110491 rows from the application_price table. Also it is Using temporary; Using filesort on this table.

解释的“行”列表明MySQL会估计它必须从application_price表中检查110491行。也是临时使用;在此表上使用filesort。

I suggst you add an index for application_price that includes (storefront_id, application_id, retail_price, created) if "created" is a field of application_price. Som combination of these fields should help.

我建议你为application_price添加一个索引,包括(storefront_id,application_id,retail_price,created)如果“created”是application_price的字段。 Som组合这些领域应该有所帮助。

#1