When Indexes Attack

Jeff · 08-09-2010, 06:13 PM

I have been having a problem with one MySQL server lately that is hosting a few databases but one in particular with millions of rows of Vbulletin posts and threads. During some queries it would hang in a "Copying to tmp table" state. This was driving me crazy. I could isolate the query and although it was not the most efficient one it should not have bogged things down this bad. After much searching I came across this guys post who was having the same problem.

I began checking the table designs for the post and thread tables and sure enough there were some non-standard indexes there. There have been a few previous administrators of this database so it is quite possible they modified it in the past. I saw many duplicate indexes and removed the ones that did not come stock with VB. Restarted mysql and rebuilt the tables. The query that would take 500 seconds to complete was now finishing in under 2 seconds.

If you are having this problem and are lost, CHECK YOUR INDEXES!

12-09-2010, 04:55 AM

That was a real useful tip and I am sure that there are a lot of people having similar issues with their MySQL server and having a tough time!! Now that there is this simple explanation, I am sure they'll check their Indexes and do the needful to get rid of the problem!!

Obama320 · 11-08-2014, 03:56 AM

I began checking the table designs for the post and thread tables and sure enough there were some non-standard indexes there. There have been a few previous administrators of this database so it is quite possible they modified it in the past. I saw many duplicate indexes and removed the ones that did not come stock with VB. Restarted mysql and rebuilt the tables. The query that would take 500 seconds to complete was now finishing in under 2 seconds.

Shylock · 03-18-2024, 01:56 PM

A tale of MySQL server turmoil and triumphant resolution. Jeff's journey through database despair led to a revelation: the culprit behind sluggish queries was none other than rogue indexes. With a few swift modifications and a reboot, the once tortoise-like queries transformed into lightning-fast operations. Lesson learned: always scrutinize your indexes!

08-09-2010, 06:13 PM	#1
Jeff Administrator Join Date: Jul 2010 Posts: 402 Rep Power: 10	When Indexes Attack I have been having a problem with one MySQL server lately that is hosting a few databases but one in particular with millions of rows of Vbulletin posts and threads. During some queries it would hang in a "Copying to tmp table" state. This was driving me crazy. I could isolate the query and although it was not the most efficient one it should not have bogged things down this bad. After much searching I came across this guys post who was having the same problem. I began checking the table designs for the post and thread tables and sure enough there were some non-standard indexes there. There have been a few previous administrators of this database so it is quite possible they modified it in the past. I saw many duplicate indexes and removed the ones that did not come stock with VB. Restarted mysql and rebuilt the tables. The query that would take 500 seconds to complete was now finishing in under 2 seconds. If you are having this problem and are lost, CHECK YOUR INDEXES!
	Reply With Quote

12-09-2010, 04:55 AM	#2
kajara23 Guest Posts: n/a	That was a real useful tip and I am sure that there are a lot of people having similar issues with their MySQL server and having a tough time!! Now that there is this simple explanation, I am sure they'll check their Indexes and do the needful to get rid of the problem!!
	Reply With Quote

11-08-2014, 03:56 AM	#3
Obama320 Junior Member Join Date: Nov 2014 Posts: 1 Rep Power: 0	I began checking the table designs for the post and thread tables and sure enough there were some non-standard indexes there. There have been a few previous administrators of this database so it is quite possible they modified it in the past. I saw many duplicate indexes and removed the ones that did not come stock with VB. Restarted mysql and rebuilt the tables. The query that would take 500 seconds to complete was now finishing in under 2 seconds.
	Reply With Quote

03-18-2024, 01:56 PM	#4
Shylock Junior Member Join Date: Mar 2024 Location: Amsterdam Posts: 1 Rep Power: 0	A tale of MySQL server turmoil and triumphant resolution. Jeff's journey through database despair led to a revelation: the culprit behind sluggish queries was none other than rogue indexes. With a few swift modifications and a reboot, the once tortoise-like queries transformed into lightning-fast operations. Lesson learned: always scrutinize your indexes!
	Reply With Quote

configuring mysql

mysql best practices

mysql queries

other mysql