MySQL Forums
Forum List  »  Performance

Fulltext optimization
Posted by: Mitja
Date: December 09, 2004 12:19PM


First the specifications:
MySQL version 4.0.20
news database
670.000 records
1,5 GB data file
1,2 GB index file
indexes: id, date, text (fulltext)

The problem turns up when I want to search only between certain dates e.g.:

select *, (MATCH (text) AGAINST ('string' IN BOOLEAN MODE)) AS score from table where date>='date_from' and date<='date_to' and (MATCH (text) AGAINST ('string' IN BOOLEAN MODE))

The search without (date>='date_from' and date<='date_to') clauses takes only a few seconds. Search with date selection takes app. 30 seconds. I also want to be able to sort results by date desc and show only 20 results per page but this only increases query time. I already tried numerous ideas but non seemed to help. The machine is 2,4 GHz P4, with 1 GB RAM and SCSI disks. I really don't have any other idea so any thought is very welcome.

Thank you in advance.


Options: ReplyQuote

Written By
Fulltext optimization
December 09, 2004 12:19PM
December 09, 2004 07:26PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.