Why is this query taking so long?

Question

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

# 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?

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)

If you don't already have them, I would put indexes on `application_price.application_id`, `application_price.storefront_id` and `application_price.retail_price`. — Jason Swett, May 17 '11 at 14:24
@Jason was just writing the same thing :). Also, what is the data type of the `application_price.retail_price` column? If the value can be below zero, make sure it is numeric and *isn't* unsigned — Phil, May 17 '11 at 14:26
You may also want to tweak MySQL's memory settings. I THINK, although I'm not sure, that "Using temporary; Using filesort" means that MySQL is creating some kind of temporary file on the hard disk in order to do its sorting, whereas it could potentially be doing that sort right in RAM if the memory config allowed it to do so. Don't quote me on that but I had a similar problem recently and upping the memory available fixed the problem. — Jason Swett, May 17 '11 at 14:27
Another thing that may help: make sure any columns that *could be* numeric *are* numeric. I read in High Performance MySQL that indexes are faster on numeric fields than string fields, since numeric values are of course smaller than string values. — Jason Swett, May 17 '11 at 14:28
I have indexes on application_price.application_id, application_price.storefront_id and application_price.retail_price Data type of retail price is decimal(9,2) — kitenski, May 17 '11 at 14:34
Whether memory is the problem with this query or not, I strongly recommend this tool for helping you optimize your memory configuration: https://github.com/rackerhacker/MySQLTuner-perl — Jason Swett, May 17 '11 at 14:46

score 1 · Answer 1 · answered May 17 '11 at 14:21

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

answered May 17 '11 at 14:21

Mr47

2,655
1
19
25

score 1 · Answer 2 · answered May 17 '11 at 14:30

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.

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.

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

score 0 · Answer 3 · answered May 17 '11 at 14:30

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.

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.

Why is this query taking so long?

3 Answers3