MySQL Forums
Forum List  »  Full-Text Search

Slow Full-Text Search when table grows constantly
Posted by: Claudiu Ionescu
Date: March 12, 2008 04:46PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Full-Text Search when table grows constantly
4458
March 12, 2008 04:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.