The xf_search_index table can be truncated if using XFES

Paul B

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.

phpMyAdmin
Code:
TRUNCATE 'xf_search_index';

SSH
Code:
TRUNCATE TABLE xf_search_index;
 
Last edited:
Noob question, where's the index now stored?

Also what SQL command do I use to truncate this table?
 
Noob question, where's the index now stored?

Also what SQL command do I use to truncate this table?

The index is stored in the location set up on elasticsearch on your server, not in mysql:)
 
  • Like
Reactions: DBA
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).
 
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!).
 
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.
 
@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?
Code:
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.
Code:
# 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:
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.
 
Top Bottom