Re: Stop using filesort

[Mark Duffy]

Thanks Jorgen for your help. My issue is that I have a app that doesnt have a search that we are happy with ie MyISAM FULLTEXT INDEX, so Im created two next tables to search against. We then have triggers that populate the two new fulltext search tables.

First question, Just so I'm sure, what exactly does it mean to do a "composite fulltext index" for the two search fields?

I'm going to redesign the schema, so that the new search table has only one table. So far, I'm thinking it will look like this.

id : topic_id : post_id : title : content
-------------------------------------------
1 : 1 : 1 : title1 : some content
2 : 1 : 2 : null : some more content
3 : 2 : 3 : title2 : some more content 2
4 : 2 : 4 : null : some more content 3

When I try to group by topic_id it still does the filesort? Can this not be avoided when using group or order by?

EXPLAIN SELECT
MAX(MATCH(forum_search.post_text,forum_search.topic_title) AGAINST('newsgroups' IN BOOLEAN MODE)) AS score
FROM bb_posts_topics_fulltext_search forum_search
WHERE MATCH(forum_search.post_text,forum_search.topic_title) AGAINST('newsgroups' IN BOOLEAN MODE)
GROUP BY topic_id
ORDER BY score;



Edited 1 time(s). Last edit at 05/09/2011 11:06AM by Mark Duffy.