SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Not using auto increment - bad?

    Hi

    I'm working on an application where users register, and then a row is added to about 5 more tables with more details about the user.

    I read in a guide that NOT using auto-increments is bad performance wise...
    #16: Not using AUTO_INCREMENT
    ● But wait, MySQL is highly optimized for primary there's keys created as AUTO_INCREMENTing more! integers
    ● Enables high-performance concurrent inserts
    - Lockless reading and appending
    ● Establishes a “hot spot” in memory and on disk which reduces swapping
    ● Reduces disk and page fragmentation by keeping new records together
    http://www.slideshare.net/techdude/h...ql-performance

    What I'm doing at the moment is using an ID with Primary Key and auto increment when the user registers, then I just add a row to each of the 5 tables with the users ID. But I don't use any auto increment, I just set the user ID as primary key, because I don't have any use for a another column, as it is just one row per user.

    user table
    uid (Primary Key, auto_increment, mediumint)
    name (varchar 50)
    [etc]

    team table
    uid (Primary Key, mediumint)
    [lots of other columns]

    another table
    uid (Primary Key, mediumint)
    [lots of other columns]

    and so on.

    Did I misunderstand the guide, or should I put a column called "id" or something (to the left of uid in the 5 tables) as Primary key, auto_increment? I wouldn't use it for anything though.

    The tables will most likey be of InnoDB engine.

    I haven't had any problems with this, but I recently tried to populate the tables with a lot of dummy rows to see how well it ran with about 50 000 instead of about 100... It didn't go quite as expected, but I eventually managed to do it. Though the database has been crashing/locking/not closing tables and so on ever since, so I think something happened. Just trying to make sure it's not because of bad "table planning", which can't handle bigger amount of rows or something like that...

    Thanks in advance!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    If all 6 tables (user table and 5 other tables) all have the same key (userid) and only one row for each key value, then why do you have six tables? Put everything in one table!

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They are divided into six tables because there's a lot of data. It's for a browser game and this just felt like the best way to do it, instead of cramming 100 columns into one single table. Felt more natural to update and keep track of data separated by what the user is doing in the game, like one table for their progress in different dungeons and so on.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by korg View Post
    They are divided into six tables because there's a lot of data. It's for a browser game and this just felt like the best way to do it, instead of cramming 100 columns into one single table. Felt more natural to update and keep track of data separated by what the user is doing in the game,
    But now you have to do more than 1 db access, or join 2 or more tables together to get all the data.
    Quote Originally Posted by korg View Post
    like one table for their progress in different dungeons.
    So what does this table look like? One field with the overall progress? Or multiple fields, one for each dungeon?
    If the latter is the case, then you should normalize it: add a dungeonid field (the key would become userid+dungeonid) and have only one progress field. There would be one row for each user-dungeon.

    Anyway, the way you set things up, there is no need for a autoincrement key field in the other five tables, since you wouldn't be accessing those tables by that field anyway.

  5. #5
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply!

    The dungeon table looks like this;
    -----
    uid (primary key)
    overall
    dungeon1
    dungeon2
    dungeon3
    [up to 10]
    (these are int or bigint and can look like "11100000" where 1 indicates completed, and 0 not completed)


    Each user also has 3 teams they control, so there are 3 of those (dungeon) tables. Identical, but with different values depending on which team it is.

    There are also team tables for each team where wins, losses and lots of other stats are recorded.

    There's only 1 of these 6 (miscount...) which actually has an additional index (apart from the primary key uid) which needs to be used for longer queries. That is the table for the main team which has 2 indexes.

    The users table is not updated very often at all, only if you edit your personal details basically. The dungeon tables are only updated when you complete a quest. The team tables on the other hand, are updated much more frequently, especially the main team table (which had 2 indexes). Lot's of duels between players are played by the server every minute or so and the users involved have their row in the main team table is updated with new stats etc.

    Sorry if it's explained poorly.

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look into database normalization. anytime you have fields in a table like dungeon1, dungeon2, dungeon3 etc. then your data is likely not normalized.

    as for splitting your table, you should understand performance-wise, when properly indexed with the database properly normalized, that mysql can handle millions of rows in a table. you have reduced performance by splitting your tables so they won't be "too big" when in fact they are most likely small in size.

  7. #7
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, what you are saying is something like this would be better;

    table_dungeons
    dungeon_id
    dungeon_name
    dungeon_something_info

    table_userdungeons
    uid
    dungeon_id
    dungeon_progress

    Is this what you (both of you?) try to tell me? Then there would be like 10 rows for each user in table_userdungeons with different dungeon_id's.

    Because if that's so, I just don't understand why in this case. The only reason would be if I want to add more dungeons, or need fields like "dungeon_name" etc, or am I missing something important?

    I have normalized other tables like this, for example equipment, which is equipped on and off, users buy and sell, and I add more etc. That I can understand.

    as for splitting your table, you should understand performance-wise, when properly indexed with the database properly normalized, that mysql can handle millions of rows in a table. you have reduced performance by splitting your tables so they won't be "too big" when in fact they are most likely small in size.
    They are very small in size, even with a million rows in each, that's true. Should I look into just using one table for most of the info for each user? The main reason I separated them was because one table with 100 columns seemed so much, and I thought it would be easier to keep track of everything.

    Thanks for your input!

  8. #8
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do it like this:
    Code:
    table1
    - id: INT unisigned autoincrement
    - ...
    
    table2
    - id: INT unisigned 
    - ...
    
    table3
    - id: INT unisigned 
    - ...
    Then, in your code you insert a record in table1, get it's auto increment ID, and insert records using that ID in the rest of the tables.

  9. #9
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have a table with 100 columns, and you sometimes need to add/remove columns, then your DB structure is wrong.

    Consider making more tables like this:
    - Main table with just a few columns (ex: email, pass, userName, etc)
    - Data table with the different columns you had previously (armor, weapon, xStats, yStats, etc) as records, and with the columns (data_table_id, dataType, dataDescription)
    - Link table between your main table and your data table (main_table_id, data_table_id, dataValue)

    That takes a bit more codding, but once you do it once, you can add/remove "stats" at will pretty much.


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
  •