SearchCore_Text Too Large

[DRJ]

I am having issue with a large site where the server is locking up. One thing we found was that the searchcore_text table is 4.3 GB. I am wondering if this table can be truncated and would just start rebuilding itself as needed or if deleting all the data would cause problems. It is running v4.2.0.

[Zachery]

That table hold all of your searchable text in the entire software. IF you truncate it, you won't have any search results.

[DRJ]

Ok, thanks.

Are there any recommendations for when this tables gets this large? The host is saying that queries to this table and the posts table are taking a while to update which is causing a backlog for everything else and then the database crashes and needs to be reset.

[Wayne Luke]

Set all your tables except the search*_text and *session to innodb. You shouldn't as many problems with them creating a backlog.

Search Updates should also be queued so they are handled in blocks instead of attempted immediately. Settings -> Options -> Message Searching Options.

Upgrade MySQL to 5.5.X

[hilary]

Hi DRJ,

They post is a bit old - but I'm having the exact same issue. Our searchcore_text is 4GB and our SQL is crashing each morning.

We are having to repair the table each day - but then it just crashes again the next morning.

How did you end up overcoming this?

Would really appreciate some information about the process!!!

Thanks!

[Zachery]

On a dedicated server, you should look into sphinx.