Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002

    Unanswered: Designing a Fast Table/DB layout

    Hello, I am a *newbie* in a sense that I have never had any hardcore education in Database design. I have done a ton of stuff working with PHP, Perl and MySQL. But I don't have much experience making my Databases good. Well, I am starting a new project and would like some input on what I think will be a pretty darn fast DB.

    The website will be mostly used by people who login if valid user are sent an Authentication code(in a cookie). This code is checked everytime they goto another page.

    This is how I plan to layout the user table, which just stores a incrimented ID(int) and the Username(char).
    CREATE TABLE users (
      id int(11) NOT NULL default '0',
      username varchar(30) NOT NULL default '',
      PRIMARY KEY  (id,username)
    From my understanding of the MySQL docs this should provide a pretty fast and efficant lookup method for the ID number which is used in all my other tables.(Because the only inserts to this will be on User Joins, which are one of the most non-frequent actions on any website, so MySQL willl cache all of the table.)

    My next table is the authentication table which will be accessed on every page load, where it compares the Users Auth code sent via the cookie and the one in the DB; if they match then it contiues loading the page. At the same time it will update the time collum with the current time(side ? should I use MySQL's NOW() function, or send it in my script -> mysql connection?). Here is the layout:
    CREATE TABLE auth (
      id int(11) NOT NULL default '0',
      auth_code char(125) NOT NULL default '',
      time int(11) NOT NULL default '0',
      PRIMARY KEY  (id)
    The limiting factor I see with this page is that on the update of the time collum, from my understanding of the mySQL docs, it will clear the cache of the table. Also this table will have frequent updates for every user login and logout, along with a serverside deamon which looks for any auth code that hasn't been used in the last 30 mins(1800 secs) ->"UPDATE auth SET auth_code = "" WHERE time <= NOW()-1800")

    The next big section will store all the preferences(and all the next 12 of my tables follow this same basic layout) It has an index of the ID obtained from the User table.
    CREATE TABLE preferences (
      id int(11) NOT NULL default '0',
      password varchar(20) NOT NULL default '',
      d_lang tinyint(4) NOT NULL default '0',
      region tinyint(4) NOT NULL default '0',
      email varchar(100) NOT NULL default '',
      icq int(11) NOT NULL default '0',
      PRIMARY KEY(`id`)  
    Any suggestions on how to make it fast/less mem hog/etc are welcome!


  2. #2
    Join Date
    Apr 2002
    The only problem that I can forsee is with locking on the auth table.

    If you use the non transactional table types the entire table will lock on every page access, this could cause big problems under heavy load.

    Even if you are using innoDB tables you will have to ensure that any updates reference a unique key (can auth_code have a unique index?) to ensure that the lock is at row level. Another potential problem with the server side daemon as this will also lock the table (not too bad as this is only every 30 mins).

    Hope this is helpful,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts