Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.

Cleaning Database

+3 votes
1,089 views
asked Nov 2, 2014 in Q2A Core by Alexxx
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.

3 Answers

0 votes
answered Nov 4, 2014 by Scott

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.

commented Nov 4, 2014 by Alexxx
qa_titlewords -?
commented Apr 27, 2016 by q2apro
@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.
0 votes
answered Nov 4, 2014 by sama55
edited Nov 4, 2014 by sama55

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.

0 votes
answered Jan 13, 2015 by Joey Novak

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;
...