Fulltext search with order by static field

Hi there…

I need to optimise:

SELECT * FROM table WHERE MATCH(text_field) AGAINST (‘text’) ORDER BY score DESC;

where score is a pre-computed int field.

At the moment, despite text_field and score both having indices, the query results in a filescan.

I guess as mysql will only use one index per table, it is using the fulltext index.

Is there anything that can be done to speed this up?


Alastair James

How many rows are in this query?
How much buffer do you have to utilise?

Not really relevant but worth a say, in my opinion, a much better fulltext alternative is sphinx (www.sphinxsearch.com)

Extra: Using where

Any thoughts?
MySQL FullText search is only use fulltext search index if full text search is performed. This means state=wa will be done using post filtering (by rea

in this case can we order table with “ALTER TABLE ORDER BY score DESC” and all next selecting do without ORDER BY statement?