how to prevent mysql from filesorting

MySQL & MariaDB Other MySQL® Questions
1

two tables are as follows .

CREATE TABLE table_a ( id int(11) NOT NULL, col1 date NOT NULL, PRIMARY KEY (id), KEY col1 (col1))

CREATE TABLE table_b ( id int(11) NOT NULL, col1 varchar(255) NOT NULL, PRIMARY KEY (id))

and then a query is

select a.id, b.col1, a.col1 from table_a a, table_b b where a.id=b.id order by a.col1 desc;

this query always does filesort. why?
col1 column on table_a is arleady indexed.
does simple ‘order by’ query on joined tables always filesort?

how can i prevent mysql from filesorting.
help me please…

2

I see two indexes on table b which are not declared in your sql create statement so first make sure you show it all.
optimize table might does it.

3

thanks for your reply.

but, there are no indexes on table b.
there is a just only primary key on table because other columns are not used in where clause.

4

This might be the same thing I was seeing for grouping, check this post.

Basically, MySQL is refusing to use in-memory temp tables for the sort because of the presence of your varchar column. You can verify if that’s the case by checking SHOW STATUS LIKE ‘Created_tmp_disk_tables’. In my grouping case, even when there were only a few rows in my tables, it would still create the temp table on disk instead of in memory.

Thing you can try:

  • normalizing your data (good for grouping, not so much for sorting)
  • changing the column to CHAR
  • If you can’t change the data model easily, you can try putting your server’s temp data directory on a tmpfs partition or other memory-based file system. This will speed things up (at the cost of RAM), but not nearly as much avoiding the filesort altogether.