MySQL Performance: FullText => Slow Full-Text Search when table grows constantly

Slow Full-Text Search when table grows constantly [message #2752]

Tue, 18 March 2008 02:20

cod_ferrow
Messages: 1
Registered: March 2008

Junior Member

Hello,
I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields that contain over 20 000 characters on each row.

The thing is, as I could observe, that when multiple different full-text searches are made in appropiate period of time, the query doesn't take so much to respond (though i couldn't quite say it's fast), but when no search is made, let's say, for over 2-3 hours, the same query takes up to 30-40 seconds to respond.

The query is simple:
SELECT
id
day_processed,
date_processed,
site_id,
MATCH (content1, content2) AGAINST ('some text here')
FROM details
WHERE (1 = 1)
AND MATCH (content1, content2) AGAINST ('some text here' IN BOOLEAN MODE)

The table is defined like this:
CREATE TABLE `details` (
`id` int(11) NOT NULL auto_increment,
`day_processed` date default NULL,
`date_processed` datetime NOT NULL default '2008-01-01 00:00:00',
`url` varchar(500) NOT NULL default 'http://',
`content1` varchar(1000) character set utf8 default NULL,
`content2` varchar(20000) character set utf8 default NULL,
`site_id` int(3) unsigned NOT NULL default '0',
`price` varchar(15) default NULL,
`phone` varchar(50) default NULL,
`email` varchar(40) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `url` USING HASH (`url`(200)),
KEY `date_processed` (`date_processed`),
KEY `id_site` (`id_site`),
KEY `day_processed` (`day_processed`),
FULLTEXT KEY `content` (`content1`,`content2`)
) ENGINE=MyISAM AUTO_INCREMENT=156243 DEFAULT CHARSET=ascii ROW_FORMAT=DYNAMIC

The MySQL Server is configured as Dedicated Server Machine (with MySQL Instance Configurator), it has dual core processor and 2Gb of RAM.

I would appreciate any suggestion for ways to improve speed. Please let me know if you need any additional information.

Best regards

Report message to a moderator