1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.4 Finding Double Posts

Discussion in 'Troubleshooting and Problems' started by markku, Dec 30, 2014.

  1. markku

    markku Well-Known Member


    Is there a SQL query or something I could find all double posts with?

    I'd be happy with getting the post IDs and then manually going to the forum and deleting them. If it cannot be automated.

    Thanks in advance!
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    This is an expensive query:

    SELECT p1.post_id AS 'original post id'
    	, p2.post_id AS 'possible duplicate post id'
    	, p1.message
    	, p1.username
    	, p1.user_id
    FROM xf_post AS p1
    INNER JOIN xf_post AS p2 ON (
        p2.message = p1.message
        AND p2.post_date BETWEEN p1.post_date AND p1.post_date+100
        AND p2.user_id = p1.user_id
        AND p2.post_id != p1.post_id
        AND p2.post_id BETWEEN 1 AND 5000
    WHERE 1=1
    AND p1.post_id BETWEEN 1 AND 5000
    ORDER BY p1.post_id
    I added the red pieces to operate on a small subset of my post table because it was taking too long to process all 130k posts.

    This query will return post_ids for posts with the same message by the same user that were made within 100 seconds of each other.
    markku likes this.

Share This Page