[TYPO3-dev] indexed_search kills server

[Steffen Kamper]

"Martin Kutschker" <martin.kutschker-n0spam at no5pam-blackbox.net> schrieb im 
Newsbeitrag 
news:mailman.1.1211364144.10828.typo3-dev at lists.netfielders.de...
> Steffen Kamper schrieb:
>> Hi,
>>
>> on my server i encountered massive problems with indexed_search.
>>
>> Have a look to query times:
>>
>> # Time: 080521 10:18:31
>> # User at Host: web2[web2] @ localhost []
>> # Query_time: 555  Lock_time: 0  Rows_sent: 30  Rows_examined: 21263272
>> SELECT IR.phash
>>                         FROM index_words IW,
>>                                                 index_rel IR,
>>                                                 index_section ISEC
>>                         WHERE
>>                                 IW.baseword LIKE '%einrichtung%'
>>                                                 AND IW.wid=IR.wid
>>                                                 AND ISEC.phash = IR.phash
>>                                                 AND ISEC.rl0 IN (1)
>>                                                  AND is_stopword=0
>>                         GROUP BY IR.phash;
>> # Time: 080521 10:28:30
>> # User at Host: web2[web2] @ localhost []
>> # Query_time: 599  Lock_time: 0  Rows_sent: 960  Rows_examined: 21270094
>> SELECT IR.phash
>>                         FROM index_words IW,
>>                                                 index_rel IR,
>>                                                 index_section ISEC
>>                         WHERE
>>                                 IW.baseword LIKE '%von%'
>>                                                 AND IW.wid=IR.wid
>>                                                 AND ISEC.phash = IR.phash
>>                                                 AND ISEC.rl0 IN (1)
>>                                                  AND is_stopword=0
>>                         GROUP BY IR.phash;
>>
>> This causes a very high server load and my only chance is to deactivate 
>> the indexed_search.
>> So any hints are welcome.
>
> Deactivate string part matching, allow only "starts with". Any search with 
> "LIKE '%foobar%' will never be abale to use an index and depending on the 
> size of your index it will kill peformance.
>
> Solution: switch to mngosearch or wait for Stucki's Mysql FULLTEXT search.
>
> Masi

thx, i look forward for a long time solution.

vg Steffen