XF 1.5 xf search index table larger than everything else?

TUG

TUG

Active member
so I realize that our forum gets a ton of searches and im fine with the search index table being of a significant size...

however at this point our xf_search_index table is larger than everything else combined nearing 4gigabytes alone for this table. im not a database guy, but it would seem that having an index thats actually larger than the data being indexed seems...off? (xf_post for example is only 2gb in size)

question is twofold:

1. is there a way to reduce the size of this index while still maintaining an effective search index?(not only for practical reasons, but it also seems ridiculous to be backing this table up every night as well)
2. after reducing its size, is there something i should do (or should have done) to prevent it from growing this far out of control to this point? I didnt actually think you needed to rebuild/maintain this particular table on a regular basis? perhaps I was wrong?

thanks for any suggestions or input in advance! I plan on upgrading the xen installation within the next month and was just looking into why the mysql db was so large before the upgrade!
 
Sort by date Sort by votes
ok..long as its not abnormal im ok with it being that size. it just seemed bizarre that it was larger than everything else combined. i thought it might be full of old info from the vbulletin migration years ago.
 
Upvote 0 Downvote
You should upgrade to Enhanced search because the default search is very slow when you have more than 500,000 posts. Also the Enhanced search uses stemming which provides better results.

1531774503192.webp
 
  • Like
Reactions: TUG
Upvote 0 Downvote
TUG said:
ok..long as its not abnormal im ok with it being that size. it just seemed bizarre that it was larger than everything else combined. i thought it might be full of old info from the vbulletin migration years ago.
Click to expand...
Well, since you need to rebuild your search index once after a migration, there's pretty much no chance of it containing old data. If you really want to be on the safe side (although it really is not needed) you could delete and rebuild your index in the admin panel.
AndyB said:
You should upgrade to Enhanced search because the default search is very slow when you have more than 500,000 posts. Also the Enhanced search uses stemming which provides better results.
Click to expand...
No clue where you got the number from, but that's not something you can generalize. Besides that, stemming has a lot of disadventages, especially when you don't have an english speaking community or your community uses mixed languages or your community can't use their language properly. Stemming actually is only useful in scientific / journalistic fields.
 
Upvote 0 Downvote
yes we went thru all the rebuild items/tasks after the upgrade and configuration was complete, ive just not run those items since.

there is no performance or disk space issue on the server itself so if its working as intended there is no need to mess with anything, i was just doing regular maint on a group of our domains and was surprised that the xen database was so much larger than the vbulletin db was (for roughly the same number of posts and users etc)
 
Upvote 0 Downvote
i am looking at the individual table sizes themselves, not combined. the overall db size is 7.x GB

the xf search table itself is 4.x gb
the xf post table is the next largest at 2gb

no other table is even close
 
Last edited:
Upvote 0 Downvote
What I mean is something like this:
28589099537_87bd7b379a_o.png

Since the (MySQL) index is on the actual posts and titles as I mentioned earlier (fulltext key), it's pretty normal that your (XF) search index is almost double the size of your posts + threads + profile comments tables.
 
Upvote 0 Downvote
You must log in or register to reply here.
Top Bottom