Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: using innodb engine for my database

    Hi,
    I am using PHP5 with MYSQL, and i started designing database for my application. I had used INNODB engine and my script is here. I am going to design database like this for all other tables in my application.
    1. Does this script is good for handling very large number of records?
    2. Also consider thousands of users are querying this database at the same time, will it executes faster?
    3. Also i had used CACADE UPDATE and DELETE, when i delete the record from the parent table, the child records are deleting, but not updating.

    Code:
    CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,
                        username VARCHAR(20) NOT NULL,
                        PRIMARY KEY(id)
                        ) ENGINE = INNODB;
    CREATE TABLE profile (id INT NOT NULL AUTO_INCREMENT,
                          userid INT,
                          INDEX user_id (userid),
                          PRIMARY KEY (id),
                          FOREIGN KEY (userid) REFERENCES users(id)
                          ON UPDATE CASCADE ON DELETE CASCADE 
                          ) ENGINE = INNODB;
    
    CREATE TABLE privacy (id INT NOT NULL AUTO_INCREMENT,
                          profileid INT,
                          INDEX profile_id (profileid),
                          PRIMARY KEY (id),
                          FOREIGN KEY (profileid) REFERENCES profile(id)
                          ON UPDATE CASCADE ON DELETE CASCADE
                          ) ENGINE = INNODB;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [QUOTE=bharanidharanit;6445956]
    1. Does this script is good for handling very large number of records?

    yes, up to 2 billion for each table


    2. Also consider thousands of users are querying this database at the same time, will it executes faster?

    faster than what?

    actually, it depends on the query -- if you did a search for user 42 or profile 1234 or privacy 83625 it would be extremely efficient, but if you did a search for username 'todd' it would be very slow


    3. Also i had used CACADE UPDATE and DELETE, when i delete the record from the parent table, the child records are deleting, but not updating.

    i guess you did not test the update correctly

    if you had a user, let's say 'todd', with a user id of 57, please explain under what circumstances you would want to change todd's userid to some other number?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Also consider thousands of users are querying this database at the same time, will it executes faster?
    I asked about query.
    Sorry update works, actually i mistook, when i add a new entry to the parent table, i thought the userid will be updated automatically to the child tables also, but i found it updates, only if the userid found on child tables.

Posting Permissions

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