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

XF 1.5 How do I find the count of a particular keyword in all posts?

Discussion in 'XenForo Questions and Support' started by Stuart Wright, Jan 19, 2016.

  1. Stuart Wright

    Stuart Wright Well-Known Member

    A forum search produces only 902 posts in the results, presumably because the number of results is limited.
    I tried a database search in PHPMySQL but timed out, unsurprisingly, since we have over 22 million posts.
    How can I find out the number of instances of a particular key word in all visible posts?
  2. Fred.

    Fred. Well-Known Member

    Just go to the Tag manager in the ACP and check the total count.

    edit: I should read better :oops:
    Last edited: Jan 19, 2016
  3. wang

    wang Well-Known Member

    This sql query mighr be of help.

    SELECT COUNT(*) As Count FROM  `xf_post` WHERE message LIKE  '%keyword%';
    This will return the count of all instances that the word keyword has been used in all posts.
  4. Brogan

    Brogan XenForo Moderator Staff Member

    If the above query is the one you were using which was timing out, you can try limiting it to a subset of posts like so:
    SELECT COUNT(*) FROM `xf_post` WHERE message LIKE '%keyword%' AND post_id BETWEEN 1 AND 10000;
    wang likes this.
  5. wang

    wang Well-Known Member

    Thank you for adding the part for limiting it to a subset of posts sir. When I posted the query, I forgot that the OP mentioned that the sql search timed out because of the huge amount of posts.
  6. Stuart Wright

    Stuart Wright Well-Known Member

    Thanks for the replies.
    I ran 23 instances of
    SELECT count(post_id) FROM `xf_post` WHERE message_state = 'visible' and message like '%Samsung%' and post_id > 0 and post_id <= 1000000
    in the end, changing the number to do a million at a time. I'm adept at SQL, just wondered if there was an alternative way to do it.
    I wanted to know how many posts had the word 'Samsung' in them and it turns out it's 412,000. There's an addon needed here, I think.

Share This Page