[TYPO3] Slow query

Martin Kutschker Martin.Kutschker at n0spam-blackbox.net
Thu Sep 28 09:18:47 CEST 2006


Lukas schrieb:
> My web hotel gave me an alert for a slow SQL query that took 16 seconds. 
> When I ran the same question in PHPmyAdmin, it took 6 seconds, which is 
> shorter but still pretty long.
> 
> I guess the query came from macina-searchbox. How can I speed it up?
> 
> Here's the query:
> SELECT IR.phash
> FROM index_words IW,
> index_rel IR,
> index_section ISEC
> WHERE
> IW.baseword LIKE '%ängsholmmen%'
> AND IW.wid=IR.wid
> AND ISEC.phash = IR.phash
> AND ISEC.rl0 IN (130)
> AND is_stopword=0
> GROUP BY IR.phash;

Get rid of "column like '%term%'" constructs because it may not use an 
index. That means you may only allow "begins with" ("like 'term%'") or "is 
exactly" (= 'term') for your web users. But I don't think that the indexed 
search really optimizes these cases.

Maybe it helps to use a MySQL specific full text index for "contains" 
searches. But essentially that mould mean you would have to create a text 
field with all (unqiue) words on the page to allow a Mysql full text index. 
But of course this also has to be multiplied with the language/cHash/grlist 
matrix.

Masi



More information about the TYPO3-english mailing list