vb.org Archive - MYSQL Query to count words written

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)

- - MYSQL Query to count words written (https://vborg.vbsupport.ru/showthread.php?t=324109)

grey_goose

01-02-2017 03:47 PM

MYSQL Query to count words written

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

Code:

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	01-02-2017 03:49 PM

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

01-02-2017 04:21 PM

...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	01-02-2017 04:24 PM

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

01-02-2017 04:33 PM

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	01-03-2017 07:07 PM

Quote:

Originally Posted by grey_goose (Post 2580196)

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.

All times are GMT. The time now is 08:48 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01247 seconds Memory Usage 1,718KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (1)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (6)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: