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