Hello All,
I'm looking for a way to improve a query using fulltext index and so far,
couldn't find any good solution.
I have an alert table (described below) with around 6 million records and a
full-text index on these two columns (ALERT_COMMENT, ALERT_SOURCE). I'd like to
query the latest 30 received alerts based on a string content. Currently, I am
proceeding with the following query:
SELECT * FROM Alert
WHERE MATCH(ALERT_COMMENT, ALERT_SOURCE) AGAINST ('string content' IN BOOLEAN
MODE) > 0.0
ORDER BY ALERT_ID DESC
LIMIT 30;
However, this query is taking minutes to retrieve the data, likely due to poor
performance of the full-text index in conjunction with the ORDER BY statement.
When I remove the 'ORDER BY' statement, the result is returned in seconds.
Do you have any suggestions for improving the retrieval time for this query?
Below is the table structure:
CREATE TABLE `Alert` (
`ALERT_ID` int(11) NOT NULL AUTO_INCREMENT,
`SITE_ID` varchar(50) NOT NULL,
`ALERT_LEVEL` int(4) NOT NULL,
`RECEIVED_TIME` datetime NOT NULL,
`OCCURRED_TIME` datetime NOT NULL,
`ALERT_SOURCE` varchar(50) NOT NULL,
`ALERT_COMMENT` varchar(1024) NOT NULL,
PRIMARY KEY (`ALERT_ID`),
FULLTEXT KEY `IX_ALERT_COMMENT_SOURCE_CODE` (`ALERT_COMMENT`, `ALERT_SOURCE`)
);
Thanks in Advance
Gava
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
