samedi 27 décembre 2014

Full text search results in a large amount of time spent in 'FULLTEXT initialization'


I'm currently trying to run some queries against a data dump of Stack Overflow's comments. Here's what the schema looks like:



CREATE TABLE `socomments` (
`Id` int(11) NOT NULL,
`PostId` int(11) NOT NULL,
`Score` int(11) DEFAULT NULL,
`Text` varchar(600) NOT NULL,
`CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`UserId` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `idx_socomments_PostId` (`PostId`),
KEY `CreationDate` (`CreationDate`),
FULLTEXT KEY `Text` (`Text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


I ran this query against the table, and it ran incredibly slow (It does have 29 million rows, but it has a Full-Text index):



SELECT *
FROM socomments
WHERE MATCH (Text) AGAINST ('"fixed the post"' IN BOOLEAN MODE)


So I profiled it, the results of which are:



|| Status || Duration ||
|| starting || 0.000058 ||
|| checking permissions || 0.000006 ||
|| Opening tables || 0.000014 ||
|| init || 0.000019 ||
|| System lock || 0.000006 ||
|| optimizing || 0.000007 ||
|| statistics || 0.000013 ||
|| preparing || 0.000005 ||
|| FULLTEXT initialization || 207.1112 ||
|| executing || 0.000009 ||
|| Sending data || 0.000856 ||
|| end || 0.000004 ||
|| query end || 0.000004 ||
|| closing tables || 0.000006 ||
|| freeing items || 0.000059 ||
|| logging slow query || 0.000037 ||
|| cleaning up || 0.000046 ||


As you can see, it spends a long time in FULLTEXT initialization. Is this normal? If not, how would I fix it?





Aucun commentaire:

Enregistrer un commentaire