Joined table update query

[sybakaos]

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]

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]

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

[Boofo]

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

[Paul M]

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]

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!