SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Efficiency of altering a MySQL table

    Hello,

    I want to implement a database to keep track of users, but here is the thing. I want to be able to modify the fields that keep user information while there already are users in the database.

    For example, if I am keeping the age and location of users, and want to add, say the address, I can (of course, it will be blank for existing users). Or, if I want to remove the location, I can just delete the field (losing all data in column).

    Now, I was wondering if the most EFFICIENT way to do this would be altering the MySQL table structure. Is there a better way to do this than altering the DB structure on the fly?

    -cARL
    Last edited by Majglow; May 31, 2002 at 16:45.
    Ohai!

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    changing the table schema shouldn't be a 'day-to-day' event like SELECTs and UPDATEs are... the table schema should have been properly planned out before it was 'set in stone' (really soft stone though )... if you do find yourself needing to change the table schema, you should have to sit down and think thru your changes... you can add/remove columns on the fly using a PHP script but it's not something where you should have to be concerned with efficency/speed since it's something you do like when the moon becomes blue... or when the SitePoints server finally delivers my (long-overdue) Porsche Boxster...

  3. #3
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, it would be whenever I (or some other administrator) decides to change the fields that they want to collect from the user.

    So, it wouldn't be everyday. Hopefully, only once or twice. I was wondering if there was a better way to do it... like having a table:

    | User ID | Fild Name | Value |
    -----------------------------------------
    | 1 | Age | 25 |
    | 1 | Location | Home |
    | 2 | Age | 785 |
    | 2 | Location | a retirement home |


    Something like that...

    In this example, the table would never have to be restructured, but it would take up way more memory I think.

    What if I have a really really big user table (say 100 000 rows). How would restructuring effect the operations on the table after (like SELECT and UPDATE). Would those operations be really slowed down untill some sort of "fix up the table" command is excecuted to correctly reorganize data, etc... I don't really know much about restructuring tables, except that it can be done.... So I don't know if it's the best way to do this.

    -cARL
    Ohai!

  4. #4
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas?

    -cARL
    Ohai!

  5. #5
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sounds like an ok idea if you foresee yourself adding arbitrary columns ever so often... i've never taken a course on Database Theory so i can't comment whether this would go against any principles...

    i feel this would better belong to the database forum since it really doesn't concern PHP anymore... if you want to, i can get an Advisor to move this to the database forum so that it gets better addressed (some good db veterans there like MattR and DaveMaxwell)...

    personally, i'd go with altering the table schema if and when the need arises...

  6. #6
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, you probably are right about having the thread moved.

    Could somebody move this thread to the database forum?

    Thanks,
    -cARL
    Ohai!

  7. #7
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'll get an Advisor to move it

  8. #8
    SitePoint Wizard silver trophy Jeremy W.'s Avatar
    Join Date
    Jun 2001
    Location
    Toronto, Canada
    Posts
    9,121
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Moved, but it does need a new title... suggestions?
    SVP Marketing, SoCast SRM
    Personal blog: Strategerize
    Twitter: @jeremywright

  9. #9
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how about "Efficiency of altering a MySQL table"?
    Ohai!

  10. #10
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Did I hear my name?

    When you drop a column typically the entire table has to be re-copied to a new table.

    Basically under the hood the engine goes something like this:
    Code:
    LOCK TABLE table1
    
    INSERT INTO #temp_Table
      SELECT col1,
             col2,
             col4
        FROM table1
    
      DROP TABLE table1
      RENAME TABLE #temp_Table TO table1
    So, your entire table is locked while the rest of the data is being moved around. This is very expensive.

    Typically I do something like this in these types of situations which is pretty much like you suggested:
    Code:
    CREATE TABLE user_account(
      userid   INT AUTO_INCREMENT,
      username VARCHAR( 100 )
    );
    
    CREATE TABLE profile(
      profileid INT AUTO_INCREMENT,
      title     VARCHAR( 100 ) NOT NULL
    );
    
    CREATE TABLE user_profile(
      userid    INT            NOT NULL,
      profileid INT            NOT NULL, 
      data      VARCHAR( 100 ) NOT NULL,
    PRIMARY KEY( userid, profileid )
    ); 
    
    CREATE INDEX profileid_idx ON user_profile( profileid )
    Two integers don't require much room, and if you remove a profile field (say "Dog's Name") all you have to do is issue two deletes:
    Code:
    DELETE profile
     WHERE profileid = 1
    
    DELETE user_profile
     WHERE profileid = 1

  11. #11
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Matt, doing that, if you're trying to get data for multiple users in one query, you'll get duplicate `username' rows etc., won't you? like when displaying posts (and their user) on a forum.

    i know vBulleting uses something like that, but they add/drop columns from a userfield table or something and do some voodoo in PHP to get the names of the fields. but i'm sure you know that.

    i'm trying to figure out the best way to do this, too.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  12. #12
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, your query would be:
    Code:
      SELECT username,
             title,
             data
        FROM user_account      u_a,
       INNER JOIN user_profile u_p ON u_p.userid  = u_a.userid
       INNER JOIN profile      p   ON p.profileid = u_p.profileid  
       ORDER BY username ASC, 
                title    ASC, 
                data     ASC
    So your rows would be:
    Code:
    username | title | data
    ------------------------
    Bob Ross, CatName, Fluffy
    Bob Ross, DogName, Spike
    If you already know the user name then you can go ahead and omit that from the query so you don't have any excess data. It's somewhat 'easier' to have it all in a single row since you don't have to do client (php) fetch but generally not that much of a big deal. When you think about it, vBulletin actually does a FOR loop to generate the templates for the profile fields so it wouldn't be really that big of a programming change to have it fetch the profile rows at that time as well.

    Although if I really needed everything in a single row I'd probably use a database cursor to fetch those rows and stick them into either output variables or a temp table.

    The thing to check would be how costly it is to retrieve the (many?) profile rows for a particular user (since probably you're not showing the profiles of more than one user at a time) vs. how much it costs to drop and add columns all the time (greater liklihood with the add/drop that something goes wrong as well).
    Last edited by MattR; Jun 2, 2002 at 06:43.

  13. #13
    SitePoint Zealot raymo's Avatar
    Join Date
    Feb 2002
    Location
    The High Country, Victoria, Australia
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about a GUI? MySQL Front can alter tables easily and efficiently. Click click and you're done.

    My eyes get so tired of ascii, this thing helps keep my sanity.
    ride it like it's stolen

  14. #14
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, thanks for the info...

    Now, how would that structure impact efficency if I want to find all users that are located in the USA? Or what if I wanted to find all the users with a B-day in the month of may, then list them with some profile info?

    Do you think adding and droping fields would be more efficient in this case? How bad would be removing a field if there are ... um .. say 100 000 users?

    (All that is hypothetical).

    -cARL
    Ohai!


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
  •