List:General Discussion« Previous MessageNext Message »
From:Dan Goodes Date:April 10 2003 11:35pm
Subject:Optimising a Full-text search
View as plain text  
Hi Folks,

I have a performance-related question. The system I'm testing on is a 
RedHat 7.3 system, with two 933MHz processes, 512M of ram. SCSI disks in 
RAID1.

I have a database with one table - 3 INT(10) columns (one of them is an
auto-inc Primary Key), and a CHAR(255)  column. I have a fulltext index on
that CHAR column. At the moment, the table has 4.6 million rows. The 
database currently takes up about 630MB (approx 50% data, 50% index).

Thing is, i need to search on that CHAR column. At the moment, A fulltext 
search on two terms

MATCH (column) AGAINST ('+term1 +term2' IN BOOLEAN MODE)

returns 14200 rows in about 14-15 seconds. A search on just one term

MATCH (column) AGAINST ('term1')

returns 141000 rows in about 8-10 seconds.

While This is much much faster than using standard indexes and LIKE 
statements, it's still not ideal. What, in your opinion, will improve the 
performance of the searches - more RAM, faster CPU, faster disks.

Any help will be appreciated - if you'd like to get more info from me, 
send me email directly and we can discuss off-list.

-- 
Regards,

Dan Goodes  :  Systems Programmer  :  dang@stripped

Help support PlanetMirror - Australia's largest Internet archive
by signing up for PlanetMirror Premium : http://planetmirror.com

Thread
Optimising a Full-text searchDan Goodes11 Apr
  • Re: Optimising a Full-text searchThomas Spahni11 Apr