Mysql – Proper indexes or anything else to optimize

MySQLoptimization

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                                  | 
+----+-------------+-------+--------+---------------+---------+---------+------------------+---------+----------------------------------------------+

Best Answer

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.

Related Question