Performance Problem with full text search

[Axel Scheel]

Hello,
we're trying to do a BOOLEAN full text search over a table with about 
200.000 entrys and 650MB of data (full text index is about half the size 
of the data, the average row length is about 3,2kB) on the System stated 
below. A sample statement we're trying to run is:

SELECT
   id, title
FROM
  texts
WHERE
   MATCH(title, text) AGAINST('+strasse* +erneuerung*' IN BOOLEAN MODE);
EXPLAIN says: table        : texts
              type         : fulltext
              possible_keys: text
              key          : text
              key_len      : 0
The key text is a combined full text index over the colums TEXT and TITLE.

This kind of statement takes about 10 to 30 seconds and generates 30-40% 
CPU usage for the first time the match string is used. If the search is 
already in cache it takes from 0 to 3 seconds. But unfortunaltely we 
could not preexecute all possible searches, so the time for the first 
use of the match string is of capital importance.
Is such a full text search really that slow? Or is there any possibility 
to speed this up to about 2 to 5 seconds? Help would be very appreciated.

best regards and thanks
        Axel Scheel
System Description:
-------------------
Linux 2.4.21
CPU: AMD Duron(tm) Processor stepping 00
Detected 647.810 MHz processor.
Calibrating delay loop... 1291.05 BogoMIPS
Memory: 774500k/786368k available
MySQL Version: 4.0.13
mytop says   : Key Efficiency: 99.7%
my.cnf       : key_buffer = 256M
               max_allowed_packet = 1M
               table_cache = 256
               sort_buffer_size = 1M
               read_buffer_size = 1M
               myisam_sort_buffer_size = 64M
               thread_cache = 8
               query_cache_size= 16M
The data file (*.MYD) and the index file (*.MYI) resist on different 
hard disks. The one with the index is of type ext2 the one with the data 
is of type ext3.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]