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

[Stuart Wright]

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.]

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

edit: I should read better

 

[wang]

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.

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.

 

[Paul B]

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]

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]

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.