Hello all,
I'm having a bit of trouble with a full-text query being slow. At first I
thought it was a problem with a join, then I thought it was a problem with a
sort - but I've boiled down the query and it seems like plain-old slowness.
This is the table:
CREATE TABLE `PC1_Text` (
`AssetID` int(11) NOT NULL default '0',
`Content` text NOT NULL,
PRIMARY KEY (`AssetID`),
FULLTEXT KEY `Content` (`Content`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And here is its status:
Name: PC1_Text
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 5906
Avg_row_length: 15849
Data_length: 93608372
Max_data_length: 4294967295
Index_length: 49875968
Data_free: 0
Auto_increment: NULL
Create_time: 2005-04-09 12:25:41
Update_time: 2005-05-19 10:22:14
Check_time: 2005-05-19 09:58:00
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
The table has only 5900 rows of text and I'm trying to use the full-text
index to find hits on a search term using:
SELECT SQL_NO_CACHE AssetID FROM PC1_Text as FT WHERE MATCH(Content)
AGAINST('+"after dinner" ' IN BOOLEAN MODE);
This query takes about 1.6 seconds to execute. More general queries take
even longer. Performing an explain on the query shows exactly what I would
expect:
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | FT | fulltext | Content | Content | 0 | | 1 | Using where |
+----+-------------+-------+----------+---------------+---------+---------+------+------+-------------+
I've even taken the time to run:
LOAD INDEX INTO CACHE PC1_Text;
And it doesn't make a difference.
All this leads me to believe I am dealing with a system configuration
issue. The server is a Dual Xeon 2.8 EM64T, it has 4GB of memory and 15K
Drives. And I am currently using --memlock to ensure that mysql stays in
real memory. My config looks like the following:
[mysqld]
key_buffer_size=2500M
tmp_table_size=128M;
max_heap_table_size=128M;
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_concurrency = 8
thread_cache = 8
query_cache_size = 64M
max_connections=10000
ft_min_word_len=3
Anyone have any idea what I might try to increase the performance of this
query? I'm quite certain it should be able to do better than this.
Thanks as always!
-Dan
