Fulltext search with order by static field

Polyglot Projects Sphinx & Full-Text Search
1

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?

thanks

Alastair James

2

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)

3

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

4

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