Use MySQL fulltext keys in indexed_search
It takes several 10s of seconds to get serach result displyed.
The problem is the "fulltext" search on a regular index either on index_fulltext.fulltext or on index_words.baseword performed by a like '%nonsense%' query.
We could speed up the time (10s of sec down to millisec) by do the following:
1) adding fulltext index to index_fulltext.fulltext and index_words.baseword
2) changed query in class.tx_indexedsearch.php to use fulltext capabilites of MySql.
Limitation: requires newer versions of MySql (4.0 or later)
Find diff for latest 4.0.5 attached
(issue imported from #M5089)
#1 Updated by Peter Russ over 12 years ago
At default indexed_search queries these fields by using a "like '%whatever%'" where condition. This is not very performant. As there are not more fields required for that I see no need to blow up the fulltext index.
To make it short: the content of these columns is text ;-)
If you want to index fulltext is the only way which makes sense.
#2 Updated by Michael Stucki almost 11 years ago
Attached is a patch "indexed_search_fulltext.diff " that integrates a new extension "indexed_search_mysql" into TYPO3. The patch is made for Trunk but it should be portable to 4.2 without problems.
Proceed as following:
- Apply the patch
- Install "indexed_search_mysql"
- a) either truncate all index_* tables or b) optimize index_fulltext to create the index (unsupported, but should work)
Feedback is welcome.
#3 Updated by Michael Stucki almost 11 years ago
Additional patch "indexed_search_fulltext_4-2.diff" contains this feature plus a backport of the treelist cache, so it will result in a much faster search engine.
This patch is not targetted for integration in 4.2, so you will have to patch your 4.2 systems manually...