Slow Full-Text Search when table grows constantly

[codferrow]

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

[ronverdonk]

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR