Hi List
I have a table with a fulltext index across five fields, with about 2.2
million records and a data size of about 5.6 GB (index another 3.5 GB).
When I test a query that uses fulltext matching, the first run takes
about 15-16 seconds to complete. The second run takes about 0.1 sec and
subsequent runs will all all ge around the 0.1 sec time.
The query that I use has a call to NOW() as one of the criteria, so i
know that the query results will not be cached. Yet, the times would
suggest that some sort of caching effect is being observed.
Is there something I can do that can return more consistent query
performance - hopefully with a time somewhere between the two extremes?
The structure of the table as well as the query are below.
All help will be appreciated.
CREATE TABLE `article` (
`article_id` int(11) NOT NULL auto_increment,
`site_id` int(11) NOT NULL default '0',
`article_code` varchar(80) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`publication_id` int(11) default '0',
`rating_id` int(11) NOT NULL default '0',
`status_id` int(11) NOT NULL default '0',
`section_id` int(11) NOT NULL default '-1',
`template_id` int(11) default NULL,
`headline1` varchar(255) default NULL,
`headline2` varchar(255) default NULL,
`headline3` varchar(255) default NULL,
`live` enum('Y','N') NOT NULL default 'N',
`modified_date` datetime NOT NULL default '0000-00-00 00:00:00',
`original_date` datetime NOT NULL default '0000-00-00 00:00:00',
`flash` enum('Y','N') default NULL,
`expiry_date` datetime default NULL,
`embargo_date` datetime default NULL,
`embargo_hour` int(11) default NULL,
`embargo_day` int(11) default NULL,
`message` varchar(255) default NULL,
`section_front` enum('Y','N') NOT NULL default 'N',
`front_page` enum('Y','N') NOT NULL default 'N',
`author_id` int(11) default '0',
`is_urgent` enum('Y','N') NOT NULL default 'N',
`live_date` datetime default NULL,
`page_number` int(11) NOT NULL default '0',
`is_free` enum('Y','N') NOT NULL default 'N',
`source_id` int(11) default NULL,
`edition` int(11) default NULL,
`master_article` int(11) default NULL,
`newspapersection_id` int(11) default NULL,
`blurb` text NOT NULL,
`body` text NOT NULL,
`is_indexed` enum('Y','N') NOT NULL default 'N',
`zone` varchar(255) NOT NULL default '',
`warning` varchar(255) NOT NULL default '',
`blurb_is_intro` enum('Y','N') default 'N',
PRIMARY KEY (`article_id`),
KEY `site_id` (`site_id`),
KEY `article_code` (`article_code`),
KEY `name` (`name`),
KEY `publication_id` (`publication_id`),
KEY `rating_id` (`rating_id`),
KEY `status_id` (`status_id`),
KEY `section_id` (`section_id`),
KEY `live` (`live`),
KEY `modified_date` (`modified_date`),
KEY `original_date` (`original_date`),
KEY `expiry_date` (`expiry_date`),
KEY `section_front` (`section_front`),
KEY `front_page` (`front_page`),
KEY `live_date` (`live_date`),
KEY `is_urgent` (`is_urgent`),
KEY `page_number` (`page_number`),
KEY `author_id` (`author_id`),
KEY `embargo_date` (`embargo_date`),
KEY `master_article` (`master_article`),
KEY `newspapersection_id` (`newspapersection_id`),
KEY `site_status` (`site_id`,`status_id`),
KEY `flash` (`flash`),
FULLTEXT KEY `blurb`
(`blurb`,`body`,`headline1`,`headline2`,`headline3`)) ENGINE=MyISAM
DEFAULT CHARSET=latin1 MAX_ROWS=10000000 AVG_ROW_LENGTH=2586;
SELECT SQL_CALC_FOUND_ROWS article_id AS iArticleId, MATCH(blurb, body,
headline1, headline2, headline3) AGAINST ("microsoft") AS dRelevance
FROM article WHERE embargo_date <= NOW() AND status_id IN (-1, -6, -10)
AND site_id = 45 AND MATCH(blurb, body, headline1, headline2, headline3)
AGAINST ("microsoft") ORDER BY embargo_date DESC LIMIT 0, 25
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
