XF 1.3 XF_Saerch Too Big: 2.7 GB!

[Recep Baltaş]

Is it normal for the search table to be too big ? It is the biggest table. Even Search Index is 400 MB. What is wrong ?

I have Elasticsearch and Xenforo Advanced Search installed BTW.

 

[Paul B]

If you have ES installed, you can truncate the search index as it isn't used.

http://xenforo.com/community/threads/the-xf_search_index-table-can-be-truncated-if-using-xfes.26182/

 

[Recep Baltaş]

I am optimizing all tables ATM. After that, I will truncate it. Thanks.

 

[Recep Baltaş]

The big one is not xf_search_index. It is xf_search.

xf_search - 2.7 GiB
xf_search_index - 479.8 MiB

Can I truncate the other one too ?

 

[Paul B]

That table contains data of actual searches carried out, including new posts being fetched.

What is the date of the oldest record?

 

[Recep Baltaş]

It is now 3.4 GB.

The oldest date can be March 2014, when we started using Xenforo, swtiching from vBulletin.

Search result value was 20.000, now I have set it to 200.

How can I see the oldest entry? The table is 975 pages long.

 

[Mike]

If you were storing 20000 search results, that could certainly cause that table to be large. It stores search results for a period of time (and this includes "find new" hits). The table will have rows cleaned up over time. You can confirm this is working correctly if you look for the smallest search ID over the course of a few days.

 

[Recep Baltaş]

It is still 3.4 GB.

 

[Mike]

Run the daily cleanup cron to trim it to only searches in the last day. If you have a lot of searches (or activities that create searches), that table will contain a fair amount of data though.

 

[Recep Baltaş]

I did. Still 3.4 GB. Can I truncate that table ? Why do I need it ? I don't need the search log.

 

[Paul B]

That table contains data of actual searches carried out, including new posts being fetched.

Yes you can truncate it, but as already pointed out, if you have a lot of searches, it will increase in size again.

 

[Recep Baltaş]

Thanks a lot. I have narroved down search results and truncated the tables.