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

The xf_search_index table can be truncated if using XFES

Discussion in 'Enhanced Search Support' started by Brogan, Jan 18, 2012.

  1. Brogan

    Brogan XenForo Moderator Staff Member

    For those of you who aren't aware, if you're using XFES you can truncate the xf_search_index table, as it is no longer used.

    On a large site, that will reduce the DB size by a considerable amount, (potentially) resulting in less server space being utilised, quicker backups, etc.

    TRUNCATE 'xf_search_index';
    TRUNCATE TABLE xf_search_index;
    Kevin, Marcus, Coop1979 and 4 others like this.
  2. DBA

    DBA Well-Known Member

    Noob question, where's the index now stored?

    Also what SQL command do I use to truncate this table?
  3. Anthony Parsons

    Anthony Parsons Well-Known Member

    I believe it would be:

    TRUNCATE 'xf_search_index';
  4. DBA

    DBA Well-Known Member

    I think you forgot the "TABLE".

    This ended up working for me:
    TRUNCATE TABLE xf_search_index;
    caoanh204 and Anthony Parsons like this.
  5. Slavik

    Slavik XenForo Moderator Staff Member

    The index is stored in the location set up on elasticsearch on your server, not in mysql:)
    DBA likes this.
  6. DBA

    DBA Well-Known Member

    Assumed as much, thanks! (y)

    I dig being able to search down to single characters, which is one of the main reasons I'm using it (my niche has a lot of 2-3 letter acronyms).
  7. Anthony Parsons

    Anthony Parsons Well-Known Member

    Sorry... that was through phpmyadmin. Yes, it would need table through SSH.
  8. Anthony Parsons

    Anthony Parsons Well-Known Member

    My database is now 500Mb lighter... thanks Brogan for that tidbit.
    GeeksChat likes this.
  9. craigiri

    craigiri Well-Known Member

    Just got rid of 1.3 Gig...that should help the RAM a bit......
    Anthony Parsons likes this.
  10. MattW

    MattW Well-Known Member

    Just got rid of 15omb from my database by truncating that table (y)
  11. DeltaHF

    DeltaHF Well-Known Member

    Wish I had seen this earlier! I would recommend a sticky for this topic, and you might want to mention it as an extra step in your ES setup guides, @Slavik.

    I reduced the size of my database (imported vB 3.8 forum, 9.4 million posts) on disk from 30GB down to 6.5GB. :D This is definitely going to make backups a lot easier (and cheaper!).
    eva2000 likes this.
  12. MattW

    MattW Well-Known Member

    That's quite a reduction!
    DeltaHF likes this.
  13. Rudy

    Rudy Well-Known Member

    I lucked out and had XFES installed from day one, before we imported the forum. Our test forum, though, probably has an index table to truncate.
  14. Marcus

    Marcus Well-Known Member

    DeltaHF likes this.
  15. Floren

    Floren Well-Known Member

    @Slavik, @Brogan: I have a question about xf_search table usage. Even if I use Elasticsearch with XES 1.0.3, it still create new entries into xf_search table when I perform a search... is this normal?
    MariaDB [axivo]> truncate table xf_search;
    Query OK, 0 rows affected (0.22 sec)
    ### Search performed on website...
    MariaDB [axivo]> select search_id, result_count from xf_search;
    | search_id | result_count |
    |         1 |           22 |
    First, I thought is related to new http.jsonp.enable setting but even with it commented I still get search entries into table. I guess is for caching purposes so it returns the result directly from table? It should not IMO.
    # curl localhost:9200
      "status" : 200,
      "name" : "Tutinax the Mountain-Mover",
      "version" : {
        "number" : "1.3.1",
        "build_hash" : "2de6dc5268c32fb49b205233c138d93aaf772015",
        "build_timestamp" : "2014-07-28T14:45:15Z",
        "build_snapshot" : false,
        "lucene_version" : "4.9"
      "tagline" : "You Know, for Search"
    Last edited: Jul 30, 2014
  16. Brogan

    Brogan XenForo Moderator Staff Member

    The xf_search table stores searches which have been performed.
    That table is used for both MySQL search and ES.

    It is the xf_search_index table which isn't used when ES is installed.
    jeffwidman and Floren like this.
  17. Floren

    Floren Well-Known Member

    Thanks you Brogan, I apologize for my lack of attention.

Share This Page