Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need help to optimize the query to avoid using "Using temporary", "Using filesort".

CREATE TABLE `target_bahrain_pepsi` (   
    `id` int(11) DEFAULT NULL,  
    `col_value` varchar(50) DEFAULT NULL,     
    `source_file_id` int(11) DEFAULT NULL,   
    KEY `id` (`id`) 
) ENGINE=InnoDB



SELECT su.adbrand_id, sum(st.col_value) as amount 
FROM statex_with_id su  INNER JOIN target_bahrain_pepsi st  ON st.id = su.id 
GROUP BY su.adbrand_id 
ORDER BY amount DESC ;

Table statex_with_id has also index.

mysql> EXPLAIN select su.adbrand_id, sum(st.col_value) as amount
    -> from statex_with_id su
    -> INNER JOIN target_bahrain_pepsi st
    -> ON st.id = su.id GROUP BY su.adbrand_id ORDER BY amount DESC ;     
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+ 
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows    | Extra                                        | 
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+ 
|  1 | SIMPLE      | st    | index  | id            | id      | 58      | NULL             | 1804021 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | su    | eq_ref | PRIMARY       | PRIMARY | 4       | dashboard1.st.id |       1 | Using where                                  | 
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+
share|improve this question
1  
can ou provide create statment of statex_with_id? –  neo Jan 14 '13 at 8:14
1  
SUM() on a VARCHAR column? Why? –  ypercube Jan 14 '13 at 9:41
    
I have checked, sum() after modify the col into double(15,6) but the query still taking 2.5 sec. –  JP Chauhan Jan 14 '13 at 10:52
1  
2.5s isn't /that/ bad for summing, grouping and ordering 1.8 million records. Remember that it also depends on your MySQL config (sort_buffer_size etc), not just the query & indexing –  Phil Jan 14 '13 at 11:46
    
Provide the CREATE TABLE for the other table as well. What is the relationship between the 2 tables? –  ypercube Feb 13 '13 at 15:20

1 Answer 1

According to the MySQL docs, you can't avoid "using temporary" and "using filesort" when you have different ORDER BY and GROUP BY expressions. You need to rewrite the query--which isn't always possible--or live with the pain. (Or install faster hardware.)

About "using filesort", MySQL docs say,

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

(many lines snipped)

You have different ORDER BY and GROUP BY expressions.

About "using temporary" MySQL docs say,

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

There are many different and related reasons you probably can't avoid "using temporary" and "using filesort". The docs are pretty good about explaining the reasons.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.