how to prevent mysql from filesorting

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, b.col1, a.col1 from table_a a, table_b b where 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…

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.

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.

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.