vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)
-   -   Joined table update query (https://vborg.vbsupport.ru/showthread.php?t=243959)

sybakaos 06-04-2010 09:48 PM

Joined table update query
 
Hello,

I've ran a thread prefixes mod since 2007. Now that I have moved to vB4 I no longer wish to use it or modify that mod to become compatible, nor do I wish to use vB4's prefix system. The mod added a new field in the tread table called threadprefix. I no longer wish to this prefix system and have thus performed a query that copied the prefix from thread.threadprefix to thread.title:

update thread set title = CONCAT(threadprefix,' - ',title) where threadprefix !=''

That worked well, the query changed 400,000+ thread titles in 300 seconds. However, I'd also like to update the post title of each first post with the prefix and came up with the following possibilities:

update post left join thread on thread.firstpostid = post.postid set post.title = CONCAT(thread.threadprefix,' - ',post.title) where thread.threadprefix !='' and thread.firstpostid = post.postid

or

update post left join thread on thread.firstpostid = post.postid set thread.title = post.title where thread.threadprefix !='' and thread.firstpostid = post.postid

These queries literally take 2-4 seconds/thread or 1,2 million seconds but do what is intended. My question, is there a faster way? I really don't want to close the board for 14 days...

Ofcourse I can simply not run the query, but in that case the threads are not findable when searching for the prefix as a keyword (even after reindexing posts). The alternative, is there a way to reindex thread titles?

Thanks for any help!

Greets,
syba

sybakaos 04-18-2011 07:58 PM

If anyone wonders, I've executed the query. I've waited 13 days and 2 hours for the query to finish, and I didn't even close the board nor ran into problems. Patience prevails.

Lynne 04-18-2011 08:05 PM

13 days.... how many posts? wow.

Boofo 04-18-2011 08:33 PM

Yes, I'd also be curious in knowing that.

Paul M 04-18-2011 08:35 PM

There are two things that would have speeded that up.

1. Index firstpostid in the thread table (remove it again afterwards if you wanted).

2. Remove the thread.threadprefix !='' - you already did that in the first query - so although it means all your titles would be updated, they would all be correct, and you would have removed a "where" clause thats slowing down the query.

sybakaos 04-18-2011 08:57 PM

There were 400,000 threads with a thread prefix and those threads had about 6,500,000 replies. The board has a total of 8.8 million posts.

I'm by no means an expert, and I've constructed the above queries from reading MySQL documentation. I'm quite proud at myself for accomplishing that, at least. I hoped to gain replies here and at other boards, but my requests for help ended in vain. I finally executed the query early this month and to my delight it was a critical success. I'm running on a 12xSAS and 16GB RAM server. I think it was just a really ineffective, albeit working, query and not a server issue.

If anyone still has the old thread prefixes, the above queries with Paul's touch may help you reset the thread and post titles, which is very useful if you use SEO products. Good luck!


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

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.00996 seconds
  • Memory Usage 1,716KB
  • 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)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