I run a query on a 3 million rows table (avrg row length 2988) as
below:
SELECT COUNT(*) FROM tbl WHERE MATCH (col) AGAINST ('keyword');
The query above took from 5 - 10 seconds.
Below is MySQL (version 4.1.11) configuration:
-------------------------------------
[mysqld]
key_buffer_size = 512M
table_cache = 1024
sort_buffer_size=8M
join_buffer_size=8M
read_buffer_size=8M
read_rnd_buffer_size=8M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 8G
max_allowed_packet = 8M
skip-innodb
skip-bdb
ft_min_word_len=2
ft_max_word_len=31
thread_concurrency = 8
-------------------------
And the server Specification:
-------------------------------
CPU: 2 x Intel Xeon 2.8Ghz HT
Memory: 3G
Linux version: 2.6.9-1.667smp
Linux distribution: Red Hat 3.4.2-6.fc3
--------------------------------
Why the SELECT COUNT(*) on fulltext index slow? Please help.