updating thread viewcount locking up database

[nchoose]

I am having an issue where the update thread set views = views + 1 where threadid = someid is taking longer than normal, causing other queries to lock up which ventually leads me to reboot the server. We tried using the setting in vbulletin to update once an hour, but that just garunteed a crash every hour.

Has anyone else had any problems with this particular query?

I am running mysql 5.1.22
I am running php version PHP Version 5.2.5

is there a way to disable the view count for threads?

also we have just over 1,000,000 threads in the table... we were up at 1.6M but I was able to clean out about 500,000 of them, I have another test forum with 600,000 posts in that I have been trying to delete using the delete forum function... it just usually stops after about 20-30,000 threads are deleted. I'm looking for a better solution than that.

--------------- Added 1215189399 at 1215189399 ---------------

I am thinking of changing the index on the thread table from a btree to a hash as they are supposed to be faster for exact match lookups. Has anyone tried this before?

[Marco van Herwaarden]

Which index you want to change on the thread table. I would start by checking if al the default indexes are present.

[Paul M]

I am having an issue where the update thread set views = views + 1 where threadid = someid is taking longer than normal, causing other queries to lock up
What happens if you manually run that query for a thread using EXPLAIN.