MySQL Performance: FullText => Fulltext search with order by static field

Members

Help

Home

Home » Performance » FullText » Fulltext search with order by static field

Show: Today's Messages :: Show Polls :: Message Navigator

Fulltext search with order by static field [message #283]

Tue, 24 October 2006 02:32

al.james
Messages: 15
Registered: October 2006

Junior Member

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

Report message to a moderator

Re: Fulltext search with order by static field [message #511 is a reply to message #283 ]

Mon, 25 December 2006 20:42

bluesaga
Messages: 15
Registered: December 2006

Junior Member

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)

[Updated on: Mon, 25 December 2006 20:43]

Report message to a moderator

Re: Fulltext search with order by static field [message #573 is a reply to message #283 ]

Wed, 10 January 2007 11:31

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Fulltext search with order by static field [message #1649 is a reply to message #283 ]

Mon, 20 August 2007 00:10

albion
Messages: 1
Registered: August 2007

Junior Member

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

Report message to a moderator

Previous Topic:	Setup Sphinx
Next Topic:	fulltext search with additional condition in the where clause

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 25 05:13:18 EDT 2008

Total time taken to generate the page: 0.11382 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.