using innodb engine for my database

bharanidharanit · #1 (**permalink**) 01-28-10, 06:41

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.

Does this script is good for handling very large number of records?
Also consider thousands of users are querying this database at the same time, will it executes faster?
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;

r937 · #2 (**permalink**) 01-28-10, 10:15

[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?

bharanidharanit · #3 (**permalink**) 01-28-10, 19:16

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech