SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2004
    Location
    London
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!

    Cheers
    Russ
    http://www.clowcreative.com
    Creative Web Design Studio

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Russellclow
    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.

  3. #3
    SitePoint Member
    Join Date
    Jan 2004
    Location
    London
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 $$$$!
    http://www.clowcreative.com
    Creative Web Design Studio

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Russellclow
    unless you get someone to custom create one...which costs alot of $$$$!
    whether it costs as much as fixing a poorly designed database, you are about to find out, eh

    indexes would seem to be the first order of business, even if they're on character fields

    surely they are varchar and not text, though!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard HarryR's Avatar
    Join Date
    Dec 2004
    Location
    London, UK
    Posts
    1,376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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..


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •