CODEWITHSUNDEEP
mysqlsqlperformanceoptimizationtemp-tables
Aamir
Aamir

Reputation: 758

How to avoid temporary table in sql query?

I am running a query (taking 3 minutes to execute)-

SELECT c.eveDate,c.hour,SUM(c.dataVolumeDownLink)+SUM(c.dataVolumeUpLink) 
FROM cdr c 
WHERE c.evedate>='2013-10-19'
GROUP BY c.hour;

with explain plan -

id  select_type  table  type  possible_keys                 key  key_len  ref  rows      Extra                                         
1   SIMPLE       c      ALL   evedate_index,eve_hour_index                     31200000  Using where; Using temporary; Using filesort

I am using table(Myisam)-

Primary key(id,evedate),

with weekly 8 partitions with key evedate,

index key- on evedate,

composite index on evedate,hour.

I have changed the mysql tuning parameters from my.ini as (4GB RAM)-

tmp_table_size=200M

key_buffer_size=400M

read_rnd_buffer_size=2M

But still its using temporary table and file sort. Please let me know what should I do to exclude this.

After adding new composite index(evedate,msisdn)

I have found some changes in few queries they were not using any temporary case, even in above query if I omit group by clause, its not using temporary table.

Upvotes: 3

Views: 3141

Answers (1)

krokodilko
krokodilko

Reputation: 36107

You cannot do anything. MySql is not able to optimize this query and avoid temporaty table.

According to this link: http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html
there are two methods MySql is using to optimize GROUP BY.

The first method - Loose Index Scan - cannot be used for your query because this condition is not meet:

The only aggregate functions used in the select list (if any) are MIN() and MAX() ....

Your query contains SUM, therefore MySql cannot use the above optimalization method.

The second method - Tight Index Scan - cannot be used for your query, because this condition is not meet:

For this method to work, it is sufficient that there is a constant equality condition for all columns in a query referring to parts of the key coming before or in between parts of the GROUP BY key.

Your query is using only a range operator : WHERE c.evedate>='2013-10-19', there is no any equality condition in the WHERE clause, therefore this method cannot be used to optimize the query.

Upvotes: 1

Related Questions