Monday, 12 August 2013

MYSQL Routine with fulltext search on temp table. Performance Issues

MYSQL Routine with fulltext search on temp table. Performance Issues

I have a table Called "Item" that is INNODB and has the following layout:
ID DESCRIPTION ->fulltext index.
DATE
The Fulltext Index is set to index words that are 1 character,
unfortunately I can't get around this requirement. The following Routine
runs fine when a user searches larger words such as "item 59929", however
some situations will result in a search like "item 1 a 22 4 c" where the
spaces have to remain that way. The database CRAWLS when these 1 character
searches are executed. What is the best way to handle this in the routine?
I did hit a road block because it seems that you can not create full text
index on temporary tables with the innodb engine, they have to by myisam.
Here is a simplified version of what i am trying to achieve:
drop temporary table if exists tmpFilter;
CREATE TEMPORARY TABLE tmpFilter(
ID INT,
Description Varchar(320),
Date datetime,
PRIMARY KEY(ID)) ENGINE=MYISAM; -- HAS TO BE MY ISAM as of 5.6 FULL TEXT
ON TEMP INNODB IS NOT ALLOWED
ALTER TABLE tmpFilter ADD FULLTEXT INDEX(description); -- ADD FULL TEXT
ALTER TABLE tmpFilter DISABLE KEYS;
INSERT INTO tmpFilter
SELECT ID, Description, Date
FROM description
WHERE date > DATE_SUB(NOW(), INTERVAL 15 MONTH)
AND description LIKE CONCAT('%item 1 2 a 4%');
AND MATCH (description) AGAINST ('"1 2 a 4" @30' in boolean mode);
ALTER TABLE tmpFilter ENABLE KEYS;
SELECT *
FROM tmpFILTER
WHERE MATCH (description) AGAINST ('+item +1 +2 +a +4' in boolean mode);
I have tried a few different things but they are making absolutely no
difference. Thanks in advance!

No comments:

Post a Comment