Bug #17054

Use MySQL fulltext keys in indexed_search

Added by Peter Russ over 14 years ago. Updated almost 3 years ago.

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

0%

Estimated time:
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)


Files

class.tx_indexedsearch.php.diff (1.72 KB) class.tx_indexedsearch.php.diff Administrator Admin, 2007-02-28 14:54
indexed_search_fulltext.diff (78.4 KB) indexed_search_fulltext.diff Administrator Admin, 2008-11-08 20:09
indexed_search_fulltext_4-2.diff (108 KB) indexed_search_fulltext_4-2.diff Administrator Admin, 2008-11-08 20:17
#1

Updated by Peter Russ over 14 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 over 12 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 over 12 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 over 10 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 almost 9 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 almost 3 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF