Delete duplicate questions created due to spam

Question

Hi,

Due to some reasons I have plenty of duplicate questions. So what is the most system friendly way to remove them.? Please advice.

Comments

Delete it from admin area..than recalculate all stuffs..do not delete directly from the database..

---

How to delete it from admin area ? There is no place where we can select multiple questions and delete together. We need to open each question and flag them then only we can delete them right ? It is not easy in my case. There are around 31k questions to remove. Please advice.

---

Unfortunately, there is not option to delete in bulk. You have to do it one by one. I know it's pain in....or you or someone write a plugin for it.

Answer 1

Found a solution myself. Here are the steps :

Wrote a query to hide all the duplicate posts.

UPDATE `qa_posts`JOIN (    SELECT title, MIN(postid) minID    FROM `qa_posts`    GROUP BY title    HAVING COUNT(*) > 1) t2 ON qa_posts.title = t2.title AND qa_posts.postid != t2.minIDSET qa_posts.type = "Q_HIDDEN";

Then delete all the hidden posts in a single click from admin->stats page.

After that re-index the content. You are good to go :)

Comments

Where  have to place this code exactly ?

---

No need to place anywhere. Just run the mysql query in your mysql query browser. Make sure you have db backed up. I am not responsible for any data lose :) Just in case ;)

---

This "SQL Query" will be too slow for large tables.
I think you must write a PHP script. As the first select query with group by and  detect the id of duplicated content than delete the row.

---

Make sure if you run direct query to database than don't forget to recalculate all points and other require stuffs. However, personally, I would never recommend to run direct query. Instead use Q2A core function to delete question based on query result.

---

This is the only way possible. Yes it is terribly slow too. Took more than a day to complete on my db which have around 6 lakhs questions. As jatin said, make sure that you do all possible indexing from QA admin panel after the query. Other wise you will still find duplicate content somewhere. Also result in to a unstable system.

---

Thanks, UPDATE `qtoaqb_posts`JOIN (    SELECT title, MIN(postid) minID    FROM `qtoaqb_posts`    GROUP BY title    HAVING COUNT(*) > 1) t2 ON qtoaqb_posts.title = t2.title AND qtoaqb_posts.postid != t2.minID SET qtoaqb_posts.type = "Q_HIDDEN";

---

Thanks, work great.
Query OK, 0 rows affected (1 min 14.41 sec)
Rows matched: 6496  Changed: 0  Warnings: 0

---

How delete "hidden posts with Answers"  ?

---

Can you explain the steps to me, I didn't understand