Bug #17054

Use MySQL fulltext keys in indexed_search

Added by Peter Russ over 12 years ago. Updated about 1 year ago.

Status:
Closed
Priority:
Should have
Category:
Indexed Search
Target version:
-
Start date:
2007-02-28
Due date:
% Done:

0%

TYPO3 Version:
4.7
PHP Version:
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

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)

class.tx_indexedsearch.php.diff View (1.72 KB) Administrator Admin, 2007-02-28 14:54

indexed_search_fulltext.diff View (78.4 KB) Administrator Admin, 2008-11-08 20:09

indexed_search_fulltext_4-2.diff View (108 KB) Administrator Admin, 2008-11-08 20:17

History

#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 about 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 about 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...

#4 Updated by Jochen Rieger almost 9 years ago

Wouldn't it be good if this patch will be ported to recent TYPO3 versions as well?

Maybe even include an option in indexed_search's ext conf that can be set to make use of the MySQL fulltext feature...?

#5 Updated by Steffen Gebert over 7 years ago

  • Status changed from Accepted to Resolved
  • Target version deleted (0)
  • TYPO3 Version set to 4.7

Use system extension indexed_search_mysql.

#6 Updated by Benni Mack about 1 year ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF