Mysql – Removing the “Using filesort”

Hey there, deep specific post here.

There are many options out there, but MySQL is, by far, the most used free RDBMS. Nevertheless it is pretty common for us, developers, to be sloppy when it comes to optimize our queries. Of course, as I said here, premature optimization is somewhat dangerous, but I’m talking about these times when you really need to bring some query execution time down.

One of the greatest villains of MySQL query optimization is the dreaded “Using Filesort”. Trying to order something without an index can, in extreme cases, kill your query.

Trivia: Don’t be fooled by it’s name: “Using filesort” is, in it’s core, a quick sort (ignore the “file” in it’s name).

How does it happen

Almost any order query without indexes. My example will show a two fielded order by clause:

CREATE TABLE IF NOT EXISTS `nice_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `field_1` varchar(255) NOT NULL,
  `field_2` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
EXPLAIN SELECT field_1, field_2
FROM nice_table
ORDER BY field_1, field_2

Running this query will give something like this:

ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 SIMPLE nice_table ALL (null) (null) (null) (null) 30 Using filesort

“Using filesort”, bad bad bad. Let’s solve this:

ALTER TABLE nice_table ADD INDEX idx(field_1, field_2);

With the index, it’ll order in the index itself, avoiding any post-query process to order the result set.

explain SELECT field_1, field_2
FROM nice_table
ORDER BY field_1, field_2;
ID SELECT_TYPE TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROWS EXTRA
1 SIMPLE nice_table index (null) idx 1534 (null) 30 Using index

Now that’s better! Of course, in small tables it does not even make a difference in query time, but watch out in huge databases. This will cause a great performance boost.

Big hugs!

Leave a message

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s