MySQL Forums
Forum List  »  Optimizer & Parser

Re: Stop using filesort
Posted by: Mark Duffy
Date: May 09, 2011 10:57AM

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?

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.

Options: ReplyQuote

Written By
May 06, 2011 02:14PM
May 08, 2011 01:28PM
Re: Stop using filesort
May 09, 2011 10:57AM
May 10, 2011 04:40AM
May 10, 2011 11:41AM
May 10, 2011 12:54PM
May 12, 2011 08:04AM
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.