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