[TYPO3-dev] Indexed Search Performance Improvement

[Peter Russ]

Steffen Kamper schrieb:
> "Martin Kutschker" <Martin.Kutschker at n0spam-blackbox.net> schrieb im 
> Newsbeitrag 
> news:mailman.1.1172673126.18249.typo3-dev at lists.netfielders.de...
>> Peter Russ schrieb:
>>> It takes several 10s of seconds to get search result displayed.
>>>
>>> The problem is the "fulltext" search on a regular index either on 
>>> index_fulltext.fulltext or on index_words.baseword performed by a
>>> "where index_words.baseword like '%nonsense%'" query.
>> Quite obviuos ;-)
>>
>>> We could speed up the time (10s of sec down to millisec) by doing the 
>>> following:
>> You say "could" but give a number. Does this mean you have tried it and 
>> your site experiences this perfomance boost?
>>
>>> Limitation: requires newer versions of MySql (4.0 or later)
>> IMHO we should move on to 4.0 or later for TYPO3 4.2 anyway. But what 
>> about DBAL? Not all DBs come with a full text indexed and if the 
>> definition and query syntax will be different. It'd be fine if this can be 
>> handled gracefully.
>>
>> Or you simply create an XCLASS extension for this. But I don't know how to 
>> add the changed index in the ext_tables.php.
>>
>> Masi
> 
> Hi,
> 
> i asked for that index also a while ago. The answer was, we can't change to 
> be compatible to DBAL.

100% compatible == 100% not usable :-(

> But why don't filter that index in DBAL ?
> 
> vg Steffen 
> 
> 
Solution could be a DB specific hook. But at the moment there's none at 
this point. IMHO the best place would be:

function getResultRows_SQLpointer($sWArr,$freeIndexUid=-1)	{
	// This SEARCHES for the searchwords in $sWArr AND returns a COMPLETE 
list of phash-integers of the matches.

	if ($hookObj = &$this->hookRequest('getPhashList'))	{
		$list = $hookObj->getPhashList($sWArr);
	} else {
		$list = $this->getPhashList($sWArr);
	}
	
	// Perform SQL Search / collection of result rows array:
	if ($list)	{
....


Regs. Peter.


-- 
Fiat lux!
Docendo discimus.
_____________________________
4Many® Services
openBC: http://www.openbc.com/go/invuid/Peter_Russ