0

I want to create a database with 3 main entities each of which will be subtyped further(by subtype I mean generalization-specialization).

This database will support a website where the users can ONLY Read the database. I don't know how many users will hit the website daily. As I see vision it, the database will have a TOTAL of 500,000 rows.

Once a week I would like to update the database with around 10 records in ALL in various entities.

Should I use InndoDB? What optimizations can I make since this will be a "mostly read" database. Is it sensible to stop the website for 5 minutes and upload the weekly 10 records? Or can that be done online?

This is an example in MariaDB. Although I have 3 entities,for the sake of a simple example I have used ONE entity Person which has subtypes.

DROP DATABASE IF EXISTS Person_Database;
CREATE DATABASE Person_Database;

Use Person_Database;

People are classified into 2 categories - Insider(s) and Outsider(s) Each person is EXACTLY ONE out of Insider or Outsider. This is an example of Gen-Spec, since each of the 2 categories have some variables in common and some specific variables. By Gen-Spec I mean Generalization-Specialization.

CREATE TABLE Person_Category(id TINYINT NOT NULL,category CHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Person_Category(id,category)
VALUES
(1,"Insider"),
(2,"Outsider");

Insiders are further classified into ONE out of 3 categories - Team-mate(s), Alumni and Advisor(s) This is NOT a Gen-Spec pattern since ALL 3 categories have the SAME variables.

CREATE TABLE Insider_Person_Category (id TINYINT NOT NULL,
                                  category CHAR(10) NOT NULL,
                              CONSTRAINT Insider_Person_Category_PK PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO Insider_Person_Category (id, category)
VALUES
(1, "Team-mate"),
(2, "Alumni"),
(3, "Advisor");

CREATE TABLE Person(id int NOT NULL AUTO_INCREMENT,
       first_name CHAR(20),
       last_name CHAR(20),
       category_id TINYINT NOT NULL,
       CONSTRAINT Person_PK PRIMARY KEY (id),
       CONSTRAINT FOREIGN_KEY (category_id) REFERENCES Person_Category(id), 
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Insider_Person(id int NOT NULL,
                    insider_category TINYINT NOT NULL,         
                    Person_Image MEDIUMBLOB,
                    email_address VARCHAR(100),
                    Designation VARCHAR(100),
                    Biography TEXT,
                    CONSTRAINT Insider_Person_FK1 FOREIGN_KEY (insider_category) REFERENCES Insider_Person_Category(id),
                    CONSTRAINT Insider_Person_PK PRIMARY KEY(id),
                    CONSTRAINT Insider_Person_FK2 FOREIGN_KEY (id) REFERENCES Person(id)
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE Outsider_Person (id int NOT NULL,
                      Person_URL TEXT,
                  Person_Detailed_Designation TEXT,
                  CONSTRAINT Outsider_Person_PK PRIMARY KEY(id),
                  CONSTRAINT Outsider_Person_FK FOREIGN_KEY (id) REFERENCES Person(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE USER 'user1-read-only'@'localhost' IDENTIFIED BY 'password1';
CREATE USER 'user2-never-login-as-this-user'@'localhost' IDENTIFIED BY 'password2'
CREATE USER 'user3-read-n-use-procedure'@'localhost' IDENTIFIED BY 'password3';

- 'user1-read-only' is the usual read only user
- 'user2-never-login-as-this-user' is a user who has read and write permissions, but we will NEVER log in as this user,since he can directly WRITE to tables.
- 'user3-read-n-use-procedure' is a user who can read AND write to tables but ONLY via the procedure CALL. This will be the user who updates the database once a week.

GRANT INSERT,DELETE,SELECT ON TABLE *Person TO 'user2-never-login-as-this-user'@'localhost';

DELIMITER //                      
CREATE DEFINER = 'user2-never-login-as-this-user'@'localhost'
PROCEDURE Person_Database.Person_Add (_first_name  CHAR(20),_last_name CHAR(20),_category_id TINYINT,
                             _insider_category TINYINT,    
                         _Person_Image MEDIUMBLOB, 
                         _email_address VARCHAR(100),_Designation VARCHAR(100),_Biography TEXT,
                         _Person_URL TEXT,_Person_Detailed_Designation TEXT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION;
    BEGIN
    ROLLBACK;
    END;

    START TRANSACTION;
    --- Basic check before we start.
    IF _category_id NOT in (1,2)
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Invalid Category';
    ENDIF;

    --- Insert into Person

    INSERT INTO Person(first_name,last_name,category_id) VALUES (_first_name,_last_name,_category_id);

    SET @id  = LAST_INSERT_ID();

    IF _category_id=1

    --- Insert into Insider_Person

    INSERT INTO Inside_Person(id,insider_category,Person_Image,email_address,Designation,Biography) 
    VALUES                    (@id,_insider_category,_Person_Image,_email_address,_Designation,_Biography);

    ELSE

    --- Insert INTO Outsider_Person

    INSERT INTO Outside_Person(id,Person_URL,Person_Detailed_Designation) 
    VALUES 
              (@id,_Person_URL,_Person_Detailed_Designation);

    END IF;
    COMMIT;
END//
DELIMITER ;

GRANT EXECUTE ON PROCEDURE Person_Database.Person_Add TO 'user3-read-n-use-procedure'@'localhost';

Notice if we add People only via the above user defined procedure, one Person will ALWAYS have EXACTLY ONE category ( Insider/ Outsider). If the Transaction fails after adding to Person it will be rolled back so that we don't have a Person who is neither an Insider or an Outsider. Also notice how this takes care of the Shared Primary Key - (called id ( in tables Person / Insider_Person / Outsider_Person tables ) in the above example). Query : How do I ensure that a Person will be added only via the above function and not in any other way ? I will add only 10 records each week, for the rest of the time it will be a READ ONLY database. How can I exploit this and optimize it's behavior?

The SELECT queries for this database.

Only Insiders

Strategy : Join the Insiders with Insider Category and THEN Add on the attributes for Insiders from Person

SELECT * FROM Insider_Person as IP LEFT JOIN Insider_Person_Category as IPC ON IP.insider_category = IPC.id
   LEFT JOIN Person as P ON  IP.id  = P.id;

Strategy : ONLY Some people are Outsiders so we should do a LEFT JOIN

Only Outsiders

SELECT * FROM Outsider_Person as OP LEFT JOIN Person as P ON OP.id = P.id;

ALL People

Strategy : We need to use 4 LEFT JOINs since Person has ALL people, only some are in Insider_Person. Similary we may have Inside Person Categories which MAY not have corresponding Insider_Person to start with. Also we have ALL People in Person but only some are in Outsider_Person. What if by chance we have only INSIDERS ? Then we should only do a LEFT JOIN with Person_Category.That is why we have the following :-

SELECT * FROM Person as P LEFT JOIN Insider_Person as IP ON P.id = IP.id
   LEFT JOIN Insider_Person_Category as IPC ON IP.insider_category = IPC.id
   LEFT JOIN Outsider_Person as OP ON P.id = OP.id
   LEFT JOIN Person_Category as PC ON P.category_id=PC.id;
  • Should I use InndoDB? In most cases MyISAM may give more fast readings. What optimizations can I make since this will be a "mostly read" database. Create all indices optimal for each (or close to each) query, and force proper index usage in each query. Sometimes data denormalization (duplicating data, but less tables amount, joins, queries simplifying) may increase performance additionally (and disk space needed, of course). Is it sensible to stop the website .. upload .. 10 records? Or can that be done online? 10 records only? server disabling not needed for this operation. – Akina May 20 at 4:33
  • I need Foreign keys to support the Gen-Spec pattern,which is not supported by MyISAM. I think InnoDB is a must. Please correct me if I am wrong. – user2338823 May 20 at 7:06
  • In RO database FKs makes no sense, simple indices is enough. – Akina May 20 at 7:15
  • I need FK's to maintain Gen-Spec. You have a way of implementing Gen Spec without FK's ? – user2338823 May 20 at 7:28
  • I need FK's to maintain Gen-Spec. Yes - but during data change only, no maintain needed during information retrieve. Your database is RO, it is altered with trusted process only, and it uses source data which is logically correct. Of course if FKs are compulsory for you nevertheless, use InnoDB. Or maybe some tables may be InnoDB, and another - MyISAM, or even Memory. – Akina May 20 at 7:43
1

InnoDB. Period. Full stop.

Even 10 records per second, all day, is not a problem. Just do the UPDATEs or INSERTs when you need to.

If, on the other hand, you are rebuilding the entire table, then say so; there are other techniques.

You mentioned subtyping, but want is the relevance?

Please provide SHOW CREATE TABLE, even if still being sketched out. Ditto for the SELECTs and the 10 daily changes.

A FOREIGN KEY does three things, none of which is mandatory:

  • It provides an INDEX. You can provide an INDEX without having a FK.
  • It provides data integrity checks. You could debug your code instead.
  • It provides "cascading" deletes, etc. These can be done manually.

Syntax

  • The stored proc needs BEGIN...END around the body.
  • I don't see DELIMITER statements.
  • After executing the first ROLLBACK, the code proceeds to the INSERT; perhaps you wanted to exit the proc? Perhaps ELSE ... would be the way to do it.
  • Without a check for errors, an INSERT failure will not lead to a ROLLBACK.

To prevent sneaking around the stored proc, don't give anyone INSERT privilege to the tables. Only give the stored proc permission.

And don't use root (SUPER priv) for anything other than admin tasks.

| improve this answer | |
  • Dear Rick, I have edited my original post to show you what I am trying to do. Look forward to your comments. – user2338823 May 21 at 15:41
  • The biggest variable in performance is the queries. In your case SELECTs, since there are very few inserts. Hardware and tuning and etc are secondary. A poorly written SELECT, or one that is missing an INDEX, can run 100 times as slow as a better query. Tuning, etc, rarely gets you even 2x speedup. Let's see the SELECT(s). – Rick James May 21 at 20:42
  • Have added the SELECT(s) in the original query, – user2338823 May 22 at 11:59
  • I made some more comments. I see no performance issues with those queries. – Rick James May 23 at 2:57
  • Dear Rick, My apologies for the delay in response. I needed time to research and do the things you had pointed out. I have another query: I have automated the creation of 'id' in the procedure Person_Add. I have read that, that is SAFE, even if multiple people are entering data at the same time,UNLESS we use connection pooling. Is that correct ? – user2338823 May 28 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.