Using index, using temporary, using filesort – how to fix this?

by

Well, the doc gives the exact reasons when “Using temporary” will appear:

Temporary tables can be created under conditions such as these:

If there is an ORDER BY clause and a different GROUP BY clause, or if
the ORDER BY or GROUP BY contains columns from tables other than the
first table in the join queue, a temporary table is created.

DISTINCT combined with ORDER BY may require a temporary table.

If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory
temporary table, unless the query also contains elements (described
later) that require on-disk storage.

A quick scan shows that you suffer from #1.

And this blog from 2009 says that “using filesort” means that the sort can’t be performed with an index. Since you’re ordering by a computed field, that’s going to be true, too.

So, that’s what’s “wrong”.

More Related Contents:

  1. What is better subquery vs literal on IN clause in mysql
  2. SQL query return data from multiple tables
  3. Select all columns except one in MySQL?
  4. MYSQL OR vs IN performance
  5. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  6. MySQL SELECT only not null values
  7. Storing JSON in database vs. having a new column for each key
  8. What is MySQL row order for “SELECT * FROM table_name;”?
  9. Measuring actual MySQL query time
  10. MySQL SELECT increment counter
  11. MySQL select with CONCAT condition
  12. How does MySQL’s ORDER BY RAND() work?
  13. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  14. MySql views performance [closed]
  15. MySQL INSERT INTO … VALUES and SELECT
  16. Mysql select distinct
  17. How to select one row randomly taking into account a weight?
  18. “SELECT COUNT(*)” is slow, even with where clause
  19. Compare only day and month with date field in mysql
  20. MySQL – Select the last inserted row easiest way
  21. MySQL – SQL_BIG_SELECTS
  22. MySQL INNER JOIN select only one row from second table
  23. What is the default MySQL JOIN behaviour, INNER or OUTER?
  24. MySQL : retrieve a large select by chunks
  25. MySQL – insert data from another table merged with constants
  26. Insert data into table with result from another select query
  27. How to SELECT by MAX(date)?
  28. How to use Table output from stored MYSQL Procedure
  29. MySQL matching unicode characters with ascii version
  30. MySQL cartesian product between two SELECT statements

Leave a Comment