WordPress.org

Ready to get started?Download WordPress

Forums

mySQL Efficiency? (6 posts)

  1. charismabiz
    Member
    Posted 7 years ago #

    Say I have information about accounts; account basic information and account extended information. The account information is used often due to profile searches, while the account profiles is only accessed when the user actually clicks on a profile. What's more efficient?

    1)One table with the account information along with the account profile
    2)Two tables, one with basic account info, the other with extended account info, and then doing a join when needed?

  2. drmike
    Member
    Posted 7 years ago #

    That's a toughie.

    I'd say one as you should only be pulling out the information that you need.

    I'm not an expert though and far from it.

  3. whooami
    Member
    Posted 7 years ago #

    the efficacy doesn't come from having data in one or two tables.. the efficacy is achieved by the query that retrieves the data.

    If you write efficient queries it wont matter.

  4. charismabiz
    Member
    Posted 7 years ago #

    whooami: won't there by increased seek/search times if often-accessed data is included in the same table as less often accessed data tho?

    ie: basic profiles would have to be scrolled and searched for (select in a loop), while extended profiles would only need to be accessed when viewing individual profiles (select 1)

    Essentially trying to find the best balance between seek/search times in one table versus extra resources required for joins

  5. pizdin_dim
    Member
    Posted 7 years ago #

    Although my experience (from a few years ago) is not with MySQL, the team I worked with did some profiling and managed to prove that comparing:

    1. one table with 20 fields where you only want 3 selected to
    2. two tables -- one with 3 fields and the second with 17

    Two conclusions were reached, based on a dataset of (I think) around 100,000 rows:

    1. It makes no discernable difference between the two scenarions when you're only selecting 3 fields.

    2. It does make a discernable differences when you have to also get some of the other 17 fields.

    Conclusion: Joins are expensive. Stick to having it in the one table, unless there's a one-many relationship

  6. charismabiz
    Member
    Posted 7 years ago #

    pizdin_dim, thanks for the info :)

Topic Closed

This topic has been closed to new replies.

About this Topic