CODEWITHSUNDEEP
mysql
Sarath
Sarath

Reputation: 93

How to avoid Order by using temporary,filesort

What index do i need to create to avoid order by using temporary, filesort

  EXPLAIN SELECT   tid, sum(count)    
              FROM test  
                 WHERE cid = 1
                 GROUP BY tid
                 ORDER BY sum(count) DESC

   1    SIMPLE  test    ref     PRIMARY,id_UNIQUE,cid   cid     4   const   2   Using where; Using index; Using temporary; Using filesort

Create table :

   CREATE TABLE test(
           cid INT,
           tid INT,
            datedm INT,
           count INT,
            PRIMARY KEY(cid,tid,datedm),
           INDEX(cid,tid,count),
           UNIQUE INDEX id_UNIQUE(cid,tid,datedm)
          );

Upvotes: 2

Views: 176

Answers (1)

gbn
gbn

Reputation: 432311

In this case you can't: the ORDER BY is on an aggregated value which can't be indexed as such.

Note your last index is identical to the primary key. However, if you reverse it to tid, cid it may help because GROUP BY implies an ORDER BY too. But, you may then have issues with the WHERE clause because of how MySQL works

Upvotes: 1

Related Questions