fulltext index. DATE The Fulltext Index is set to index w..." /> fulltext index. DATE The Fulltext Index is set to index w..." /> fulltext index. DATE The Fulltext Index is set to index w..."/>

MYSQL Routine with fulltext search on temp table. Performance Issues

1.7k views Asked by At

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 currently using:

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 item
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!

0

There are 0 answers