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

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?
 
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.
 
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;
 
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.
 
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.
 
Top Bottom