SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Load issues with good database structure

    We've focused our development on a very good database system. We use association tables to associate most of our data. Is there any way to optimize/index these because our host (hostgator) is telling is our site is loading slow because of queries getting killed off and too many processes (25) at one time.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    To start with, any column being used in a join should probably have an index defined on it.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We use joins on all of our queries since they're association queries. Forgive my ignorance, but does an index have to be unique? If I have a group_assoc table that is used very frequently but it only has 2 columns and it's always used in a way that we simply check to see if the userid and groupid match, should both columns be indexed?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    i suspect you're using EAV, which is a bad idea. please post some tables so i can tell for sure.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean like this?

    CREATE TABLE `udb_ability_assoc` (
    `ability_id` int(11) NOT NULL default '0',
    `unit_id` int(11) NOT NULL default '0'
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


    I currently have no indexes on these.

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am the other developer on this site. We use association tables for Many to Many relationships. We are not using EAV (thank god). All of our tables have a primary_key index on the id field. Our association tables have no indexes on them however.

  7. #7
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Our association tables have no indexes on them however.
    And there be the problem, most likely. Or at least a good place to start.

    You might want to share which database engine you are using as there are alot of platform-specific tuning tricks.

  8. #8
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    MyISAM as shown in the post above with the create table code. We have our column types setup with accurate depictions of what is within each column, primary keys on all of our tables with an ID column, then as I've stated our associations do not have any indexes, I'll get on that.

    Can you point me to any good resources, or is there any advice you can give to better fine-tune the database?

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've updated the database with indexes in appropriate columns. Should we be indexing all data that commonly ends up in WHERE clauses in our queries?

    I also read something about static vs dynamic field types and how they should be separated. Is that true?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Webnet View Post
    Should we be indexing all data that commonly ends up in WHERE clauses in our queries?
    in general, yes

    Quote Originally Posted by Webnet View Post
    I also read something about static vs dynamic field types and how they should be separated. Is that true?
    nope

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I stumbled on this article via google and am wondering if these tips are any good or not.

    http://forge.mysql.com/wiki/Top10SQLPerformanceTips

    Tip 22 there brings up a question. We currently do SELECTS in areas to check to see if data exists already, if it does we'll insert. Is this tip saying you can do that in 1 step?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Webnet View Post
    Tip 22 there brings up a question. We currently do SELECTS in areas to check to see if data exists already, if it does we'll insert. Is this tip saying you can do that in 1 step?
    to recap, for those people who don't want to visit that article, tip 22 is:
    Use INSERT ... ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT
    the answer is yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2004
    Posts
    613
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've gone through and adjusted indexes where I think they need to be. Our forums now have all high traffic association tables indexed, and I've indexed commonly used WHERE clauses columns within category, board, topic, and post tables. I'm not sure if there's anything else we can do to speed up the database. I'm sure there is, but not that I know of ^_^


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
  •