WordPress.org

Support

Support » Plugins and Hacks » mySQL Efficiency?

mySQL Efficiency?

  • 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?

Viewing 5 replies - 1 through 5 (of 5 total)
  • 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.

    whooami

    @whooami

    Member

    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.

    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

    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

    pizdin_dim, thanks for the info 🙂

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘mySQL Efficiency?’ is closed to new replies.
Skip to toolbar