Very slow full text search

[Jon Hood]

MYSQL ODBC 5.1.8
MYSQL Server 5.1.54

Windows Server 2003 ENT

Hi,

I am having an issue with MYSQL running full text search query it has become so slow taken about 12seconds when only last month it was about 2 seconds.

The main search table has 302,540 records

Query:

# Query_time: 32.171875 Lock_time: 0.000000 Rows_sent: 2332 Rows_examined: 465003
use evl_297;
SET timestamp=1295005234;
SELECT `catalogues_products`.`active`, `catalogues_products`.`rrpPrice`, `catalogues_products`.`brandGroup`, `catalogues_products`.`ISPCFull`, `catalogues_products`.`ISPCStandard`, `catalogues_products`.`IdCatProduct`, `fusion_data_it`.`fs_p_products`.`IdProduct`, `fusion_data_it`.`fs_p_products`.`description`, `fusion_data_it`.`fs_p_products`.`smallImageUrl`, `fusion_data_it`.`fs_p_products`.`sku`, `fusion_data_it`.`fs_p_products`.`vatCode`, `fusion_data_it`.`fs_p_products`.`packSize1`, `fusion_data_it`.`fs_p_products`.`packSize2`, `fusion_data_it`.`fs_p_products`.`recycled`, `fusion_data_it`.`fs_p_products`.`economy`, `fusion_data_it`.`fs_p_products`.`priority`, `fusion_data_it`.`fs_p_products`.`manufacturerCode` ,MATCH (`description`,`sku`,`kingfieldCode`,`spicersCode`,`integraCode`,`manufacturerCode`) AGAINST ('canvas paper' IN BOOLEAN MODE) + MATCH (`description`,`sku`,`kingfieldCode`,`spicersCode`,`integraCode`,`manufacturerCode`) AGAINST ('canvas paper' IN BOOLEAN MODE) relevancy FROM `catalogues_products` INNER JOIN `catalogues_customers` ON `catalogues_products`.`IdCatalogueProduct` = `catalogues_customers`.`idCatalogue` INNER JOIN `fusion_data_it`.`fs_p_products` ON `catalogues_products`.`idProductCatalogue` = `fusion_data_it`.`fs_p_products`.`IdProduct` LEFT JOIN `products_keywords` ON `catalogues_products`.`IdProductCatalogue` = `products_keywords`.`IdProduct` LEFT JOIN `products_sort` ON `catalogues_products`.`IdProductCatalogue` = `products_sort`.`IdProduct` WHERE `fusion_data_it`.`fs_p_products`.`active` = -1 AND `catalogues_products`.`active` = -1 AND `catalogues_customers`.`IdCustomer` = 0 AND ( MATCH (`description`,`sku`,`kingfieldCode`,`spicersCode`,`integraCode`,`manufacturerCode`) AGAINST (' canvas* paper* ' IN BOOLEAN MODE) OR MATCH (`products_keywords`.`Keywords`) AGAINST (' canvas* paper* ' IN BOOLEAN MODE) ) ORDER BY relevancy DESC;

I never had any slow log reports, but now I am receiving hundreds each day.

MYSQL CONFIG

max_connections=800

query_cache_size=256M
thread_concurrency = 8
table_cache=1520
tmp_table_size=59M
thread_cache_size=38
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=64M
key_buffer_size=1GB
read_buffer_size=2M
read_rnd_buffer_size=8M
sort_buffer_size=2M

skip-innodb

tmpdir=C:/Program Files/MySQL/MySQL Server 5.1/tmp

#server-id = 1
#log-bin = bin.log
log-slow-queries = slow.log
log-error = error.log
ft_min_word_len = 3
#low-priority-updates

interactive_timeout=80
wait_timeout=20
connect_timeout=15
#log-queries-not-using-indexes



[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Intel Xeon 8CORES 2GHZ 8GB RAM

Kind Regards,

Jon Hood