• 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?

Stuart Wright

Well-known member
#1
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?
 

Fred.

Well-known member
#2
Just go to the Tag manager in the ACP and check the total count.

edit: I should read better :oops:
 
Last edited:

wang

Well-known member
#3
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?
This sql query mighr be of help.

Code:
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.
 

Brogan

XenForo moderator
Staff member
#4
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:
Code:
SELECT COUNT(*) FROM `xf_post` WHERE message LIKE '%keyword%' AND post_id BETWEEN 1 AND 10000;
 

wang

Well-known member
#5
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.
 

Stuart Wright

Well-known member
#6
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.