Turning off the q2a search and replacing it with GCSE - help on database cleaning

Question

As far as I see, table qa_words holds all search terms of the q2a forum, and qa_contentwords maps it to the questions. In my db first one has 5.5 MB and the latter already 73 MB!

We all have problems finding stuff with q2a search, it still seems to lack a convenient search algo, see also "Improving the search" post.

This is why I decided to implement Google Custom Search, which gives the power of google search filtered to your site. The only drawback, it adds advertisement (google adwords/adsense)... what I personally dislike.

Anyways, my question for now:

Do I still need the table "qa_contentwords" after the implementation of a new search? Looking to the code, it seems really deeply implemented...

Can I easily "disable" qa_contentwords somehow? Or how can I keep it empty?

Comments

73MB is total size of DB? Or is it size adding two tables(qa_words +  qa_contentwords)?

---

qa_words = 5.5 MB
qa_contentwords = 73 MB

---

I forgot that the "related questions" might rely on these tables. But have not looked into it...

Answer 1

If you comment out this line in qa-base.php, the search-related tables will no longer be populated:

qa_register_module('search', 'qa-search-basic.php', 'qa_search_basic', '');

copied from gidgreen's answer here: http://www.question2answer.org/qa/28699/savely-reducing-the-q2a-database-size-when-using-q2as-search

NOTE:

This disables related questions as well. They will not show up anymore. To keep them alive you need to leave the search module active/registered. And then comment out qa-search-basic.php: $contentwordidcounts=array(); ... ... ... following five lines

Also commenting out:
qa_db_word_contentcount_update(array_keys($contentwordidcounts));

That is the way to go =)

Answer 2

Found part of the answer here:

Can unused words in qa_words be deleted safely?

From: http://www.question2answer.org/secure.php

To save space and time, you need not back up the following tables: qa_cache, qa_contentwords, qa_iplimits, qa_posttags, qa_sharedevents, qa_tagwords, qa_titlewords, qa_userevents, qa_userlimits, qa_words. These contain information which is temporary or which can be recalculated from other tables in the database.

 

And Scott is mentioning:

you can massively speed up reindexing if you empty the various indexing tables. Specifically:

• qa_contentwords
• qa_posttags
• qa_tagwords
• qa_titlewords
• qa_words

But question remains:

What happens exactly if I empty all tables, and not fill them up again?

E.g. similar questions not showing up? Or when typing the title in /ask are there no suggestions anymore? Etc.?

Comments

What I found:
• It seems that 'qa_contentwords' is only used for the q2a search, so could be abandoned when using GCSE. (This would save me for instance 100MB) → mysql-query: TRUNCATE TABLE `qa_contentwords`
I deleted the table, tried the search... it is still working!
• Table 'qa_words' however seems to be quite important, found it in: qa-db-selects.php (most occurences), qa-xml-sitemap.php, qa-db-post-create.php and of course qa-db-recalc.php.

---

Furthermore from qa-app-recalc.php:

^titlewords (all): index of words in titles of posts
^contentwords (all): index of words in content of posts
^tagwords (all): index of words in tags of posts (a tag can contain multiple words)
^posttags (all): index tags of posts
^words (all): list of words used for indexes
^options (title=cache_qcount|cache_acount|cache_ccount|cache_tagcount|cache_unaqcount): total Qs, As, Cs, tags, unanswered Qs

---

### From what I understand now, qa_contentwords is only used for the internal search.

See
1. qa-db-selects.php function qa_db_search_posts_selectspec() "Return the selectspec to retrieve the top question matches for a search"
2. qa-search-basic.php is calling some functions, e.g. qa_db_contentwords_add_post_wordidcounts() qa_db_contentwords_get_post_wordids() (declared in qa-db-post-update.php)

PS: What does "selectspec" stand actually for?

Answer 3

When I accidentally deleted a table qa_contentwords if the site is not working tags. Clicking on the tag vydoval empty. For me, it's the same urgent question. How to optimize the database. Even the VPS can't cope with the load.

But I recovered by pressing the button on the Stats->Database clean-up operations  And once the database is increased again.

 

http://fotohost.kz/images/2013/06/07/2EIJq.png