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 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