hands_on_MySQL_performance.sql

# Step1: Create test_schema
use test_schema;

# Step2: Create table
CREATE TABLE calendars(
	id INT AUTO_INCREMENT,
    fulldate DATE NOT NULL,
    day TINYINT NOT NULL,
    month TINYINT NOT NULL,
    quarter TINYINT NOT NULL,
    year INT NOT NULL,
    PRIMARY KEY(id)
);

# Step3: Create Procedure
DELIMITER $$
CREATE PROCEDURE InsertCalendar(dt DATE)
BEGIN
	INSERT INTO calendars(
		fulldate,
        day,
        month,
        quarter,
        year
	) VALUES (
		dt,
        EXTRACT(DAY FROM dt),
        EXTRACT(MONTH FROM dt),
        EXTRACT(QUARTER FROM dt),
        EXTRACT(YEAR FROM dt)
	);
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE LoadCalendarsWithAutoCommit(
	startDate DATE,
    day INT
)
BEGIN
	DECLARE counter INT DEFAULT 1;
    DECLARE dt DATE DEFAULT startDate;
    
    WHILE counter <= day DO
		CALL InsertCalendar(dt);
        SET counter = counter + 1;
        SET dt = DATE_ADD(dt, INTERVAL 1 day);
	END WHILE;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE LoadCalendarsWithoutAutoCommit(
	startDate DATE,
    day INT
)
BEGIN
	DECLARE counter INT DEFAULT 1;
    DECLARE dt DATE DEFAULT startDate;
    
    START TRANSACTION;
    WHILE counter <= day DO
		CALL InsertCalendar(dt);
        SET counter = counter + 1;
        SET dt = DATE_ADD(dt, INTERVAL 1 day);
	END WHILE;
    COMMIT;
END$$
DELIMITER ;

# Step4: Let's benchmark about insert
# Step4-1: With auto commit
SET autocommit = 1;
CALL LoadCalendarsWithAutoCommit('1001-01-01', 50000);

# Step4-2: Without auto commit
SET autocommit = 0;
CALL LoadCalendarsWithAutoCommit('1001-01-01', 50000);

# Step5: Let's benchmark about select
# Step5-1: Default option
SET autocommit = 1;
SELECT * FROM Calendars;

# Step5-2: Use read only option
START TRANSACTION READ ONLY;
SELECT * FROM Calendars;
COMMIT;