VB: Performance Issues.. how to fix (large sites)

[Mystikal]

Recently we've had huge problems with our dedicated server and had been unable to figure out what the culprit was. Even after running ApacheBench we'd still be confused =\

After taking a look at a lot of the slow queries that were happening, we realized something. It took some pages over 21 seconds to process because there were basically no indexes on attachment or post.

See.. we have this thread called "The Chick Thread", and it's absolutely full of attachments, 3000+ replies, 80,000+ views, etc. It really was killing the server.

Me and a friend took a look at the attachment table and the queries that were being ran, and added this index from the mysql command prompt. Obviously adding an index on a 900 MB attachment table wouldn't work from phpMyAdmin.

CREATE INDEX uin_fdat ON attachment (userid, filedata[20])

It took over 30 minutes to build this index.

This sped our site up like you wouldn't believe. Though, there were still slowdowns, so we took a look at the post table and added this index, which also took a little bit to build.

CREATE INDEX presorted ON post (threadid,visible,dateline,postid).

Since then we've had no server troubles whatsoever and have had 300+ users hitting the forums at the same time and have had no such spikes. Before this our server was crashing every hour!

Now I don't know if this was a fluke, but I'm pretty sure these indexes did a lot... although I'm hoping someone will prove me wrong here.

Just letting you guys know.