Slow search on big site

mikkeX · 2006-10-06 08:59:12

I am have buit http://www.etc.se with Textpattern and I have a big problem with the buitin Search – it is slow – the database is huge – so I think this is the problem – are there any tricks to speed up the search in Textpattern?

(If ou would like to test search in swedish is called sök and you find it in the right coulmn)

Sencer · 2006-10-06 09:34:42

In textpattern/publish.php replace the following line (somewhere between lines 500 and 600, search for rlike):

$search = " and (Title rlike '$q' or Body rlike '$q') $s_filter";

with this
$search = " and match (Title,Body) against ('$q') $s_filter";

You can turn production-status from live to testing, and then check the bottom of the source-code on the search results-page, it should contain runtime information, and query-time. Check before making the change and after making the change.

Other than than that: You might want to try upgrading to mysql4.1+ or at least 4.0 as the fulltext-search feature had many performance improvements over mysql3.x. Next you want to check the mysql-configuration (like key_buffers etc.) are configured with sane values for the server. Or ask the dba/hoster to do this, if you have no idea what I am talking about.

mikkeX · 2006-10-06 09:45:55

Thanks, that really speeded up things.

Another thing – is it possible to change the search so it split up the search – if I search for two words now the search find every article that has one of the two words, not both as i like it to be.

zem · 2006-10-07 03:15:12

If you can, try svn rev 1885.

hcgtv · 2006-10-07 04:33:24

I tried r1885, if I search for Textpattern Mary, it returns 7 posts, of which only 2 have both the words.

Is this what you wanted to test for?

zem · 2006-10-07 22:57:39

r1885 should be doing an “all words” search. If you search for textpattern mary, it should display only articles that contain both words. (Previous revs did an exact phrase search).

My testing shows that it works as expected. Are you sure some results have only one of the words? It’ll search the title and body.

What MySQL version are you using?

Last edited by zem (2006-10-07 22:58:43)

zem · 2006-10-07 23:52:43

Actually, I can reproduce the ‘or’ behaviour – it looks like fulltext search behaviour is unpredictable. It should at least rank articles that contain both words before those that contain only 1.

So the question becomes: is the 4.0.4 RC1 search behaviour useful?

hcgtv · 2006-10-08 00:28:55

zem wrote:

It should at least rank articles that contain both words before those that contain only 1.

Yes it does list the articles that contain both Textpattern Mary first, then it displays the rest of the or situations.

Database: MySQL 4.0.24_Debian-10sarge2-log

Textpattern CMS

Textpattern CMS support forum

#1 2006-10-06 08:59:12

Slow search on big site

#2 2006-10-06 09:34:42

Re: Slow search on big site

#3 2006-10-06 09:45:55

Re: Slow search on big site

#4 2006-10-07 03:15:12

Re: Slow search on big site

#5 2006-10-07 04:33:24

Re: Slow search on big site

#6 2006-10-07 22:57:39

Re: Slow search on big site

#7 2006-10-07 23:52:43

Re: Slow search on big site

#8 2006-10-08 00:28:55

Re: Slow search on big site

Board footer