The cost of MySQL filesort and what's the solution?

Question

I'm new to optimizing MySQL (cheers for having a high traffic site!) and I'm a little confused by filesort. I stumbled onto this article: http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ with some quick google searching but I can't seem to find any solutions. Does it mean my queries are merely poorly written, do they lack proper indexes (I've spent some time indexing properly, at least what I think is proper) or is there something else at play here?

Answer 1

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. (via mysqlperformanceblog)

So, most likely, you've got a query which isn't making use of a table index. See MySQL Create Index

Bonus!: If you're new to tuning your mysql server for high performance take a look at this:

wget mysqltuner.pl # quite the clever domain name ;)
chmod +x mysqltuner.pl
./mysqltuner.pl

This script will identify a lot of common mistakes, and make performance suggestions.