MySQL Forums
Forum List  »  Optimizer & Parser

Re: Stop using filesort
Posted by: Jørgen Løland
Date: May 10, 2011 04:40AM

As mentioned, MySQL has two options when it comes to providing ordered rows:
* access the rows through an index over the columns used for ordering, or
* filesort

Example:

CREATE TABLE search (
id INT PRIMARY KEY,
topic_id INT NOT NULL,
post_id INT NOT NULL,
title VARCHAR(10),
content VARCHAR(100)
);
INSERT INTO search VALUES (1,1,1,"title1", "some content");
INSERT INTO search VALUES (2,1,2,null, "some more content");
INSERT INTO search VALUES (3,2,3,"title2", "some more content 2");
INSERT INTO search VALUES (4,2,4,null, "some more content 3");

EXPLAIN SELECT topic_id, count(*) FROM search GROUP BY topic_id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	search	ALL	NULL	NULL	NULL	NULL	4	Using temporary; Using filesort

CREATE INDEX topic_id_idx ON search(topic_id);

EXPLAIN SELECT topic_id, count(*) FROM search GROUP BY topic_id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	search	index	NULL	topic_id_idx	4	NULL	4	Using index

As you can see, MySQL has to filesort the rows before doing GROUP BY if you don't have an index on the interesting column (topic_id), but when we add this index filesort is not needed. More on this here http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

So MySQL can use an index to resolve ordering. However, in your case it is most likely more efficient to access the table through the FULLTEXT index instead:

CREATE FULLTEXT INDEX ft_idx ON search(title,content);

EXPLAIN 
SELECT *, MATCH(title,content) AGAINST ('foo bar') AS score 
FROM search 
WHERE MATCH(title,content) AGAINST ('foo bar' IN BOOLEAN MODE) 
GROUP BY topic_id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	search	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort

EXPLAIN 
SELECT *, MATCH(title,content) AGAINST ('foo bar') AS score 
FROM search FORCE INDEX(topic_id_idx)
WHERE MATCH(title,content) AGAINST ('foo bar' IN BOOLEAN MODE) 
GROUP BY topic_id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	search	index	NULL	topic_id_idx	4	NULL	10	Using where

As you can see, you have the choice of getting cheap MATCH...AGAINST handling by using the fulltext index or getting ordering for GROUP BY for free by using topic_id_idx. You can't get both because only one of the indices is used. MySQL prefers the FULLTEXT index, but you can force it to use the other one instead if you think you know better than MySQL ;)

Filesort does not have to be a big issue, though. If the search does not return a great number of matching rows, the filesort will (despite it's name) probably happen in memory anyway.

A few notes:
1) You don't fill in "title" in all rows with the same topic_id. This saves some space but keep in mind that MATCH returns different values for the rows below. In other words, the ordering you do in the end may not be exactly as you want it:

SELECT title, content, MATCH(title,content) AGAINST ('fooo baar' IN BOOLEAN MODE) AS score FROM search;
...
fooo yet more baar 2
NULL yet more baar 1

2) Are you sure you want score to be IN BOOLEAN MODE? This mode will make score 0,1 or 2, which is not that good for ordering in the end.

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com

Options: ReplyQuote


Subject
Views
Written By
Posted
5741
May 06, 2011 02:14PM
2476
May 08, 2011 01:28PM
1747
May 09, 2011 10:57AM
Re: Stop using filesort
3629
May 10, 2011 04:40AM
1689
May 10, 2011 11:41AM
2593
May 10, 2011 12:54PM
1789
May 12, 2011 08:04AM
1379
May 12, 2011 08:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.