# how to prevent mysql from filesorting

**URL:** <https://forums.percona.com/t/how-to-prevent-mysql-from-filesorting/869>\
**Category:** Other MySQL® Questions\
**Created:** [August 6, 2008, 2:18am UTC](https://forums.percona.com/t/how-to-prevent-mysql-from-filesorting/869 "2008-08-06T02:18:12Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![sweet](https://avatars.discourse-cdn.com/v4/letter/s/c2a13f/32.png) [@sweet](https://forums.percona.com/u/sweet)\
**Post date:** [August 6, 2008, 2:18am UTC](https://forums.percona.com/t/how-to-prevent-mysql-from-filesorting/869/1 "2008-08-06T02:18:12Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![csmiesing](https://avatars.discourse-cdn.com/v4/letter/c/3e96dc/32.png) [@csmiesing](https://forums.percona.com/u/csmiesing)\
**Post date:** [August 11, 2008, 8:51am UTC](https://forums.percona.com/t/how-to-prevent-mysql-from-filesorting/869/2 "2008-08-11T08:51:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sweet](https://avatars.discourse-cdn.com/v4/letter/s/c2a13f/32.png) [@sweet](https://forums.percona.com/u/sweet)\
**Post date:** [August 12, 2008, 3:17am UTC](https://forums.percona.com/t/how-to-prevent-mysql-from-filesorting/869/3 "2008-08-12T03:17:04Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\
**Post date:** [August 13, 2008, 1:07pm UTC](https://forums.percona.com/t/how-to-prevent-mysql-from-filesorting/869/4 "2008-08-13T13:07:36Z")

</div>

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.