mySQL - rename Post `title` to match Thread `title`

[Rik Brown]

Can anyone advise some mysql queries to loop through the title of each thread in the thread table and, if the thread's corresponding posts in the post table have a different title, to update the post title to match the thread title?

Basically, I cannot understand how to loop through a table (have been Googling many pages on the matter). I'm assuming one would make a join on the two tables and then simply loop row-by-row based on thread title.

For testing/viewing purposes on one forum, I've made the following query:

SELECT thread.threadid, thread.title, post.postid, post.title
FROM thread
JOIN post
WHERE post.threadid = thread.threadid
AND thread.forumid IN (174) 
AND thread.title != post.title
ORDER BY thread.threadid, post.postid
LIMIT 30

This finds the post titles that no longer match their corresponding thread titles. It appears all I would need to do now is loop through the table row-by-row and "update" the post title to match the thread title.

Does anyone know how to accomplish this using mysql?

Thanks. -- Rik

[Farcaster]

If I understand your goal correctly and you want to update EVERY post in your database to have the same title as the parent thread, then I believe this code will work for you:

[SQL]UPDATE post, thread
SET post.title = thread.title
WHERE post.threadid = thread.threadid
AND post.title <> thread.title[/SQL]

Try it on your test forum first to make sure it works as you expected.

[Rik Brown]

Originally Posted by Farcaster

If I understand your goal correctly and you want to update EVERY post in your database to have the same title as the parent thread, then I believe this code will work for you:

[SQL]UPDATE post, thread
SET post.title = thread.title
WHERE post.threadid = thread.threadid
AND post.title <> thread.title[/SQL]

Try it on your test forum first to make sure it works as you expected.

Farcaster:

I tried it first with a single thread and then an entire forum. It worked perfectly in both instances and was very fast. However, running it against the entire vb database brought the system to a virtual crawl (we have 1.2 million posts). Since limiting it to a forum was quick, I'm going to batch it up that way in the future.

I really banged my head against the wall on this matter for about 10 hours until I read your reply.

Thank you so very much! -- Rik

--------------- Added [DATE]1232525499[/DATE] at [TIME]1232525499[/TIME] ---------------

Further to my post above....

I was able to bring into sync all thread titles and their corresponding post titles. I had accumulated over 100,000 mis-matches in 4-5 years. I had to do it forum-by-forum as mentioned above in order to limit the demands on the server. But now that its done, I can run the query against all forums quickly -- as long as I keep everything fairly up-to-date (perhaps via cron).

Thanks again, Farcaster. It should help our Google ranking. -- Rik

[Marco van Herwaarden]

[sql]AND post.title <> thread.title[/sql]
This is not really needed, as it would not change the result.

But why do you want to have all posts have the same title as the thread? Doesn't make much sense to me.

[Rik Brown]

Originally Posted by Marco van Herwaarden

[sql]AND post.title <> thread.title[/sql]
This is not really needed, as it would not change the result.

But why do you want to have all posts have the same title as the thread? Doesn't make much sense to me.

Because users leave messages like "Help" or "Does anyone else have this problem?" or "Newbie question" which aren't very useful to our users or the search engines and do not match up to the pagetext. So we tend to change those threads to titles that are more accurate and appropriate to the search engines such as:

2001 Toyota Camry - When to change the serpentine belt?
Making a Cross-Wind Landing at London Gatwick Airport
Bikes: Shimano derailleurs vs. Campy - Which are Best?

Some threads may already have tons of replies. We can just change the thread title (and even en masse via queries) and run the query above to update all those post titles. Like I said above, yesterday we fixed well over 100,000 changes that have accumulated over the years. And, our forums look much more better.

Regards. -- Rik

ps: I forgot to mention about the "AND post.title <> thread.title". I believe its quite necessary. Otherwise all posts (we have 1.2 million) would be updated each time the query is run and that would be a real drag on the server. Instead, with this limiting line of code it just updates those posts that do not match and thus limits the load on the server and the time to run the query.

[Farcaster]

Originally Posted by Rik Brown

ps: I forgot to mention about the "AND post.title <> thread.title". I believe its quite necessary. Otherwise all posts (we have 1.2 million) would be updated each time the query is run and that would be a real drag on the server. Instead, with this limiting line of code it just updates those posts that do not match and thus limits the load on the server and the time to run the query.

SQL performance can be a funny thing. It really depends. If it cannot make use of an index on the post and thread titles, then it might actually be less expensive to do a blind update. You'd have run an EXPLAIN on this to see how it is building the query.

[Rik Brown]

Originally Posted by Farcaster

SQL performance can be a funny thing. It really depends. If it cannot make use of an index on the post and thread titles, then it might actually be less expensive to do a blind update. You'd have run an EXPLAIN on this to see how it is building the query.

Will do. Thanks again. -- Rik

[Marco van Herwaarden]

Instead of duplicating the thread title to all posts, it might have been better to remove the title for these posts.

[Rik Brown]

Originally Posted by Marco van Herwaarden

Instead of duplicating the thread title to all posts, it might have been better to remove the title for these posts.

Marco:

I can't imagine any advantage to having no post title in contrast to having the correct post title. We are simply making sure that the thread title we either fix or enhance (for Google purposes, etc.) is likewise applied across the thread instead of having post titles like "Newbie Question."

Regards. -- Rik

--------------- Added [DATE]1233105651[/DATE] at [TIME]1233105651[/TIME] ---------------

As I mentioned above, I decided to spread the query forum-by-forum so it doesn't attempt to do everything at once and bring the server to its knees. Here is the code for a mysql STORED PROCEDURE that I'm currently using:

BEGIN
	DECLARE count INT default 0;
	SET count = 1;

	WHILE count <= 550 DO
		UPDATE post, thread SET post.title = thread.title WHERE post.threadid = thread.threadid AND post.title <> thread.title AND thread.forumid = count;
		SET count = count + 1;
	END WHILE;
END

We only have a little over 100 forums but they are spread over forumids 1-514 at the moment. The above simple procedure bypasses forumids that do not exist and runs the one line query only on each forum that does exist.

I'm sure this could be improved with more coding (please feel free to advise) but it does what I need without knowing much about writing stored procedures.

I hope that this helps someone else.

Regards. -- Rik

[Marco van Herwaarden]

99% of the post on a typical forum does not have a title. What you are doing now is only increasing your database size (probably a lot even) with duplicate data.