Web Analytics Made Easy -
StatCounter Separate tables - CodingForum


No announcement yet.

Separate tables

  • Filter
  • Time
  • Show
Clear All
new posts

  • Separate tables

    Let's say you have a simple member login table :

    [color=dark-blue]id | username | password | email | user_type | last_log | IP | session | active | posts |[/color]

    Then you have a member infos table

    [color=dark-blue]id | first_name | last_name | address | city | country | zip | contact | website [/color]

    Then you have a third table containing different field regarding the type of user

    [color=dark-blue]id | company | jobs_posted | ppl_employed | user_rating[/color]


    [color=dark-blue]id | jobs_found | employer_rating[/color]

    Performance-wise, Is it better to keep these tables separated, or do you think it wouldn't lower performances to bind them ?
    (for the third table set, all collumn would be there with null values for empty fields...)

  • #2
    id keep em seperate, easier to manage.
    php & asp tutorials - the birthplace - biorust - photoshop and web technologies


    • #3
      The fields in tbl 1 and tbl 2 have only one entry per user. SO binding them could be a possibility. The queries then run onto it would be simpler.
      But since tbl 1 is accessed all the time, and infos in tbl 2 are displayed only occasionally, I made it two tables. My question is more a general question about mysql performances:

      Does a table with less fields produce faster search results?


      • #4
        Does a table with less fields produce faster search results?
        To my knowledge, no. The number of records, rather than the number of fields should determine the search time, particularly when searching on an indexed field. There may be a slight delay on returning fields, due to increased parsing, but I don't know if there are any benchmarks on that.

        You could easily enough test it -- create a two tables with identical numbers of records, but different numbers of fields (but the queried fields should be duplicated exactly). Run the same queries on each, and grab the execution time of each query. Run it a bunch of times using different queries.
        Strategy Conscious