XF 1.4 XenForo slow search performance compared to vBulletin (proof inside)

[imthebest]

Hi,

On my vBulletin forum, I can search for the posts made by a user by running the following URL: search.php?do=finduser&u=xxx. This returns 250 search results in less than 0.5 seconds.

On the same database but now on XenForo, I can perform the same search with the following URL: search/member?user_id=xxx&content=post

This returns 200 search results in 4.5 seconds:

# Time: 140905  3:08:46
# User@Host: mydb[mydb] @ localhost []
# Query_time: 4.505875  Lock_time: 0.000029 Rows_sent: 200  Rows_examined: 511
SET timestamp=1409904526;
SELECT search_index.content_type, search_index.content_id
                        FROM xf_search_index AS search_index

                        WHERE MATCH(search_index.title, search_index.metadata) AGAINST ('+(_md_user_xxx) +_md_content_post' IN BOOLEAN MODE)


                        ORDER BY search_index.item_date desc
                        LIMIT 200;

Same server. The stats of the board are: 90k threads, 1m posts. The user xxx has around 270 posts.

Why the standard search system built in XenForo is slower? Do this have anything to do with MyISAM vs InnoDB? I know the solution is to use the official Enhanced Search add-on but I'm interested to know why this noticeable performance difference.

Thanks,
Super120

 

[imthebest]

@Mike could you please confirm if this is the expected performance or maybe I'm doing something wrong...

 

[Mike]

Different queries will have different performance characteristics. Different designs will lead to different trade offs. MySQL's full text search is simply not good unfortunately.

As such, for larger forums, elasticsearch is consistently recommended.

 

[0ptima]

As such, for larger forums, elasticsearch is consistently recommended.

What is considered a large forum?

 

[Paul B]

Typically 1 million posts seems to be the agreed cut off point with regards to MySQL search.

 

[Mike]

I would recommend it for any size forum simply because it can give better results (no stopword issues, proper relevancy options, including relevancy customization, etc) on top of being faster and more scalable.