XF 1.5 xf search index table larger than everything else?

[TUG]

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!

 

[S Thomas]

How many threads, posts, users and profile posts do you have? It might be worth looking into ElasticSearch, though.

 

[TUG]

200k threads, 2m posts.. 90k users roughly

 

[S Thomas]

Yea, the size looks fine with those numbers. The search index contains whole posts and titles, so well..
ElasticSearch most likely won't reduce the size, but it will speed up your search results.

 

[TUG]

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.

 

[AndyB]

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.

 

[S Thomas]

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.

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.

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.

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.

 

[TUG]

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)

 

[S Thomas]

Are you seeing index size + actual data size together or is the index size separate?

 

[TUG]

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

 

[S Thomas]

What I mean is something like this:

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.