Not the answer you need?
Register and ask your own question!

Fulltext search with order by static field

al.jamesal.james ContributorCurrent User Role Beginner
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

Comments

  • bluesagabluesaga Contributor Inactive User Role Beginner
    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)
  • PeterPeter Percona CEO Percona Moderator Role
    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
  • albionalbion Entrant Current User Role Beginner
    in this case can we order table with "ALTER TABLE ORDER BY score DESC" and all next selecting do without ORDER BY statement?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.