> Thanks Jorgen. You are a great help. I wouldnt pretend to know something MySQL knows. I hope I didnt come across that way. :-)
Glad I could help. And don't worry, you didn't...
> Just to be sure, I can set up the FULLTEXT INDEX, but to make sure it uses the topic_id to resolve ordering I use the FORCE INDEX topic_id_idx statement? Stopping filesort.
When you're done creating the table and filling it with data, you can run your query with EXPLAIN. That will tell you which index is used, and most likely it will be the fulltext index since that speeds up the fulltext search significantly. If you still prefer getting ordering for free instead of a cheap fulltext search, you can use FORCE INDEX to make MySQL do as you want. Normally, using FORCE INDEX is not a good idea, but in some cases it is.
To put it another way: lets say you have 1000 rows and a search term that matches 10 of these rows. Using the fulltext index, MySQL will look up only these 10 rows through the index and then perform filesort on them (and sorting 10 rows isn't bad at all). Alternatively, MySQL can use the other index to avoid filesort, but then it has to do inefficient search term matching on all 1000 rows instead.
> Also, what does boolean mode do to the score?
With boolean mode you'll get 0 for no search term hit, 1 for at least one search term hit. Thus, you won't be able to distinguish two rows that both match the search term even though one is a closer match than the other. Without boolean mode, you'll get a number better capable of indicating how close the hit is. Boolean mode is probably good in the WHERE clause but not for sorting. Try this to get an idea:
SELECT title, content, MATCH(title,content) AGAINST ('searchterm' IN BOOLEAN MODE) AS bool_score, MATCH(title,content) AGAINST ('searchterm') AS nonbool_score FROM search;
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com