[TYPO3-dev] indexed_search kills server

[Martin Kutschker]

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