Cleaning Database

Question

Very grown database. 

There are written search keywords? 

 

I want to remove them. Prompt that can be cleaned (which table)? 

That it would not affect the basic work. 

 

Through the admin tab "Statistics" can be restored.

Answer 1

The qa_words and qa_contentwords tables store that information so you could truncate those tables. But it is vital for searching so if you remove it searching will not work. Also new posts will continue to add to the tables and it will build up again.

By the way, if you truncate those tables, then reindex all the posts the tables may be smaller than they were before.

Comments

qa_titlewords -?

---

@Scott: Please consider an option to disable the core search. It fills the database a lot. I am using Google custom search engine instead for years. See also http://www.question2answer.org/qa/23585/turning-search-replacing-with-gcse-help-database-cleaning and http://www.question2answer.org/qa/28699/savely-reducing-the-q2a-database-size-when-using-q2as-search Thanks.

Also here: http://www.question2answer.org/qa/50039/needed-option-disable-question-user-event-updates-q2a-core -- I gonna try to find out if overrides work.

Answer 2

Relationship of the word (index) table

qa_words *1
  + qa_titlewords *2
  + qa_contentwords *3
  + qa_posttags *4
  + qa_tagwords *5

Maintenance of the word table with phpMyAdmin

*1 table has foreign key constraint. Therefore, if you want to maintain the word table (*1) by phpMyAdmin, you need to truncate tables under the order.

*2 > *3 > *4 > *5 > *1

Maintenance of the word table with "Stats" option of Q2A

"Admin" > "Stats" > "Reindex content"

Tables from *1 to *5 will be refleshed. However, there is one important point. Fragmentation may occur on your disk by this process. As a result, your disk may not be reduced. In that case, your database (disk) may be reduced by optimizing tables by phpMyAdmin.

Other

These operations are dangerous. I recommend that you try these operation after backing up your database. Of course, try out at your own risk.

Answer 3

I know this is an older question, but we got spammed and had to clean our db of all posts newer then a post.  We used these queries to do so.  You could remove the where statements to clear all posts and the associated qa_contentwords, etc...

delete from qa_titlewords where postid >= 17;
delete from qa_contentwords where postid >= 17;
delete from qa_posttags where postid >= 17;
delete from qa_tagwords where postid >= 17;
delete qa_words 
  from qa_words 
    left join qa_contentwords on qa_words.wordid = qa_contentwords.wordid 
    left join qa_tagwords on qa_tagwords.wordid = qa_words.wordid 
    left join qa_titlewords on qa_titlewords.wordid = qa_words.wordid 
  where  
    qa_contentwords.wordid is null and 
    qa_tagwords.wordid is null and
    qa_titlewords.wordid is null;