Skip to:
Content
Pages
Categories
Search
Top
Bottom

Re: Fulltext searching very slow


Mark Barnes
Member

@mark8barnes

Full text search is of course still quicker and getting rid of the GROUP BY helps even further. 66% quicker in my case. I’m going to use the following query as my search.

SELECT p . * , MATCH (p.post_text) AGAINST ('test') AS
search_score FROM bb_posts AS p WHERE MATCH
(p.post_text) AGAINST ('test') AND p.post_status = '0'
AND NOT p.topic_id IN (SELECT t.topic_id FROM bb_topics
AS t WHERE t.topic_status <> '0') ORDER BY search_score
DESC LIMIT 30;

The downside is that it doesn’t include topic titles in the search. You can solve this adding this search to a temporary table, running another search on topic_title, and merging the two tables together. You need quite a bit of PHP code to manage this (particularly getting the NEXT buttons to work correctly), but it’s quite possible.

Skip to toolbar