# Fulltext search with order by static field

**URL:** <https://forums.percona.com/t/fulltext-search-with-order-by-static-field/2061>\
**Category:** Sphinx & Full-Text Search\
**Created:** [October 24, 2006, 1:02am UTC](https://forums.percona.com/t/fulltext-search-with-order-by-static-field/2061 "2006-10-24T01:02:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![al.james](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/al.james/32/745_2.png) [@al.james](https://forums.percona.com/u/al.james)\
**Post date:** [October 24, 2006, 1:02am UTC](https://forums.percona.com/t/fulltext-search-with-order-by-static-field/2061/1 "2006-10-24T01:02:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bluesaga](https://avatars.discourse-cdn.com/v4/letter/b/f19dbf/32.png) [@bluesaga](https://forums.percona.com/u/bluesaga)\
**Post date:** [December 25, 2006, 8:12pm UTC](https://forums.percona.com/t/fulltext-search-with-order-by-static-field/2061/2 "2006-12-25T20:12:39Z")

</div>

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](http://www.sphinxsearch.com))

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [January 10, 2007, 11:01am UTC](https://forums.percona.com/t/fulltext-search-with-order-by-static-field/2061/3 "2007-01-10T11:01:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![albion](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/albion/32/762_2.png) [@albion](https://forums.percona.com/u/albion)\
**Post date:** [August 19, 2007, 10:40pm UTC](https://forums.percona.com/t/fulltext-search-with-order-by-static-field/2061/4 "2007-08-19T22:40:25Z")

</div>

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