How can I reindex nearly 2 mio. DB entries?

Question

I have importet my old db into the Q2A db and it works fine, but to get related questions I hit the "reindex" button in the admin pannel.

The browser stops the action after this and nearly 24 h running the script.

Reindexed 838,330 of 1,911,510 posts..

I can fire up sql statements directly to the database, but dont know which one.

I need a solution to make the process faster or the sql statement to reindex the database.

Thanks for your support.

Answer 1

Reindexing goes much, much quicker if you truncate certain tables beforehand.

• qa_contentwords
• qa_posttags
• qa_tagwords
• qa_titlewords
• qa_words

http://question2answer.org/qa/15637/long-time-to-reindex-and-refill?show=16870#a16870

Comments

We need a better solution here. Issue is since 2017 on Github: https://github.com/q2a/question2answer/issues/525

---

Reindexing is still way to slow. 500k posts cost my server about 3 hours... and it slows down the entire site. Hard time for my users and myself :(

Answer 2

Hi and thanks for your answer but I tryed it and cant truncate qa_words (...a foreign keyconstrain...).

And the speed is not much beter. Even not fast enought that the page get an timeout because after some hours the number of reindexed pages is frozen.

I have searched for the sql statement but have only found this one:

 

 "SELECT ^posts.postid, ^posts.title, ^posts.content, ^posts.format, ^posts.tags, ^posts.categoryid, ^posts.type, IF (^posts.type='Q', ^posts.posti
d, IF(parent.type='Q', parent.postid, grandparent.postid)) AS questionid, ^posts.parentid FROM ^posts LEFT JOIN ^posts AS parent ON ^posts.parentid=parent.postid LEFT JOI
N ^posts as grandparent ON parent.parentid=grandparent.postid WHERE ^posts.postid>=# AND ( (^posts.type='Q') OR (^posts.type='A' AND parent.type<=>'Q') OR (^posts.type='C
' AND parent.type<=>'Q') OR (^posts.type='C' AND parent.type<=>'A' AND grandparent.type<=>'Q') ) ORDER BY postid LIMIT #",
                        $startpostid, $count
                ), 'postid');

But this seems to be a php code mixed with sql and I dont understand it.

Is there any possibility to update the index of large databases?

Thanks for your time and help

Fabian
 

 

Answer 3

Hi, its me again,

I tried it now with Chrome to avoid the timeout and it went better but after two days I get the message: 

Please reload the page then try again 

Is there a way to do it in steps so that I can split it or a solution where only new or not already counted related entries will be recounted?

Thanks Fabian