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!