The xf_search_index table can be truncated if using XFES


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;


Well-known member
The index is stored in the location set up on elasticsearch on your server, not in mysql:)
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).


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!).


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.


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: