1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'XenForo Questions and Support' started by imthebest, Sep 5, 2014.

  1. imthebest

    imthebest Formerly Super120


    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.

    Last edited: Sep 5, 2014
  2. imthebest

    imthebest Formerly Super120

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

    Mike XenForo Developer Staff Member

    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.
    imthebest likes this.
  4. 0ptima

    0ptima Well-Known Member

    What is considered a large forum?
  5. Brogan

    Brogan XenForo Moderator Staff Member

    Typically 1 million posts seems to be the agreed cut off point with regards to MySQL search.
    0ptima likes this.
  6. Mike

    Mike XenForo Developer Staff Member

    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.
    0ptima likes this.

Share This Page