The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Speeding up a SQL Database

Hey Guys,
My names Russ, and im the lead designer of www.clubskill.com one of the largest UK online entertainment portals. We've recently purchased and slightly modified www.vgportal.com/scripts, a gaming CMS. Sadly, for one reason or another, the site is loading VERY slowly. We've checked the server, and anything that dosent run off the SQL database is absolutly fine and loads up almost instantly. So we've come to the conclusion that its the crappy coding of the CMS.

Does anybody have ANY ideas how we could speed up the loading time of the database. WE're worried that the 20,000+ uniques we get per month is going to drop if we cant get the site loading faster... ANY help would be fantastic PLEASE!

Hey Guys,
My names Russ, and im the lead designer of www.clubskill.com one of the largest UK online entertainment portals. We've recently purchased and slightly modified www.vgportal.com/scripts, a gaming CMS. Sadly, for one reason or another, the site is loading VERY slowly. We've checked the server, and anything that dosent run off the SQL database is absolutly fine and loads up almost instantly. So we've come to the conclusion that its the crappy coding of the CMS.

Does anybody have ANY ideas how we could speed up the loading time of the database. WE're worried that the 20,000+ uniques we get per month is going to drop if we cant get the site loading faster... ANY help would be fantastic PLEASE!

Cheers
Russ

Just for kicks I downloaded theold VG1 portal to take a peek. This is some scary database design. Almost every column is a text type and there are no indexes at all. If this is the same with VG2 then I'd be afraid...very afraid.

yeh I think they've improved it for VG2, but Its still a mess - sadly though its the best CMS for a gaming site out at the moment - unless you get someone to custom create one...which costs alot of $$$$!

Clustering

If your really determined to use the existing script, another avenue you could try is clustering/load balancing. MySQL AB recently launched their clustering product (which is free for non-commercial use), so i'd take a look at that..

(you can pickup very cheap intel p3 machines, so spanning your database over another 2 or 3 servers shouldn't be very expensive).

If your really desperate you can monitor which queries are using up the most CPU time with something similar to MySQL Monitor.. or just using 'SHOW FULL PROCESSLIST' when your making a couple of requests... And see if you can optimize the database to fit the where clauses it's using..