MYSQL Query to count words written

[grey_goose]

I've got a query written that more or less counts words, that I can execute on a per-user basis because it needs the userid in WHEREs.

What I'd like to do is be able to update the entire table and run it as a cron job -- but I can't figure out how to get the userid into the subquery.
X = userid
@thisuser = where I need the userids

SET @thisuser := X;
UPDATE user
SET words = (SELECT SUM(words)
FROM (SELECT LENGTH(p.pagetext) - LENGTH(REPLACE(p.pagetext, ' ', ''))+1 AS words
FROM post as p
WHERE userid = @thisuser
UNION ALL
SELECT LENGTH(gm.pagetext) - LENGTH(REPLACE(gm.pagetext, ' ', ''))+1 AS words
FROM groupmessage as gm
WHERE postuserid = @thisuser) a)
WHERE userid = @thisuser;

I'm not sure if I'm going about this the wrong way, or just can't figure out how to fetch the userid of the current row that the query is looped on.

Suggestions?

[Dave]

So if I understand you correctly, you want to convert the SQL you posted to work without a hardcoded userid and run it against all users in the database?

[grey_goose]

...Yes. Although the way you asked that makes me wonder if I'm doing something dumb *lol*

I know it's pretty spendy from running it by hand, but it's a small forum.

[Dave]

Yes this is not something you would want to do as a cronjob, as your database gets bigger, the cronjob will take more time and more load to complete.

It would be better if you run this one time and then create a hook in vBulletin that updates the `words` column every time a post is made/modified.

[grey_goose]

Good point. I knew my spider sense was tingling!

What about executing/updating it per user on MEMBERINFO pageview, since that's where the info is displayed? Probably add a quarter sec to page load, but I'll be touching it far less.

[Dave]

Good point. I knew my spider sense was tingling!

What about executing/updating it per user on MEMBERINFO pageview, since that's where the info is displayed? Probably add a quarter sec to page load, but I'll be touching it far less.

You could approach it in 2 different ways:
1. Always update it every time the profile is visited.
2. Add it once to the login of the user, then mark the user so it does not get executed again when the user is logging in. Then update it every time the user makes a post.