1

i have this query :

SELECT date(DATE_SAISIE) , count(DATE_SAISIE) as total FROM appel_offre GROUP BY date(DATE_SAISIE) ORDER BY DATE_SAISIE

I have index on DATE_SAISIE 

create index mi_date on appel_offre(DATE_SAISIE)

and this is explain cmd :

+----+-------------+-------------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+---------------+---------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | appel_offre | index | mi_date | mi_date | 6 | NULL | 25 | Using index; Using temporary; Using filesort | +----+-------------+-------------+-------+---------------+---------+---------+------+------+----------------------------------------------+

in the column extra there is Using temporary; Using filesort so I think the query will be slow, How to avoid it ?

Improve this question
9
  • Table definition would help,but the answer is that a function on a column prevents index use. Commented Feb 13, 2015 at 17:49
  • but it use index mi_date Commented Feb 13, 2015 at 17:52
  • i didn't understand what is important about order by ? Commented Feb 13, 2015 at 17:57
  • 1
    Well I dont know why it says it uses the index.It cant.Maybe COUNT is involved.But you can do it in your application,just get the whole datetime and substring it in php or whatever language you are using. Commented Feb 13, 2015 at 18:18
  • 1
    Depends of the number of rows,probably more than 10 000 and you`ll HAVE to do it in the application.Just test it,there are some data generators for mysql online Commented Feb 13, 2015 at 18:44

1 Answer 1

Reset to default
1

  • using temporary means that MySQL has to create a temporary table for the purpose of generating your results. This is because you're grouping by a function on a column. If you were simply outputting the result of a function on a column, MySQL would not have to remember that value, but because you want to group all records with that function result together, it has to store those results somewhere.

  • using filesort is an amazingly poorly-named indication that MySQL will need to sort some temporary storage in order to generate your results. This is because you're ordering by a function on a column that is in the index rather than by the column itself.

One way to get rid of the filesort, or at least to make it faster, would be to order by the same thing you're selecting:

SELECT date(DATE_SAISIE), count(*) as total FROM appel_offre GROUP BY date(DATE_SAISIE) ORDER BY date(DATE_SAISIE)

This will give exactly the same result as your query but might allow MySQL to not have to remember the DATE_SAISIE values themselves.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

So how is this answering the question?
The OP asked how to avoid using temporary and using filesort. I have explained why they are present.
thanks man it really help me to know where the problem is from ... but my column is in datetime so i have to use date() ... @Mihai propose to me to do it in my server language (java) or add a new column in type date
As @Mihai states, it all depends on how much data there will be in the table. Just because it says using temporary and using filesort does not mean that it will be slow.
it can be 100K rows ... and i try your solution to order by the same thing but i get always using temporary and using filesort

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.