MySQL: indexing

-- MySQL Server 5.7.17
-- 1. Let's create a table
CREATE TABLE `student` (
    `student_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primary key',
    `name` VARCHAR(64) NOT NULL COMMENT 'student name',
    `birthday` DATE NOT NULL COMMENT 'day of birthday',
    `sex` ENUM('Male','Female') NOT NULL COMMENT 'sex: male/female',
    `admission_year` SMALLINT(5) UNSIGNED NOT NULL COMMENT 'year of admission',
    `teach_time` SMALLINT(5) UNSIGNED NOT NULL COMMENT 'teach time, in semesters',
    `faculty_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'reference to faculty table',
    PRIMARY KEY (`student_id`),
    CONSTRAINT `FK_student_faculty` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`faculty_id`) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT='student table' COLLATE='utf8_general_ci' ENGINE=InnoDB;



-- 2. Add a procedure to fill it with test data
DELIMITER $$
CREATE PROCEDURE fillStudent(IN NumRows INT) LANGUAGE SQL CONTAINS SQL NOT DETERMINISTIC COMMENT 'procedure to fill student table'
  BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
      WHILE i <= NumRows DO
        INSERT INTO `student` (`name`, `birthday`, `sex`, `admission_year`, `teach_time`, `faculty_id`)
        VALUES (
          MD5(RAND()),
          NOW() - INTERVAL FLOOR(RAND() * 14000) DAY,
          IF (RAND() > 0.5, 'Male', 'Female'),
          FLOOR(2000 + 10*RAND()),
          FLOOR(8 + 10*RAND()),
          CEIL(RAND() * (SELECT COUNT(*) FROM faculty))
        );
        SET i = i + 1;
      END WHILE;
    COMMIT;
  END$$
DELIMITER ;



-- 3. Let's insert 10M rows (may take a long time!)
CALL fillStudent(10000000);
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 00:21:39 */
-- Table size: 895 mb



-- 4. Let's check O(N) time
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student`;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,346 sec. */



-- 5. Also let's check MySQL created index for Foreign Keys automatically:
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `faculty_id` = 3;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,608 sec. */



-- 6.1. No indexes:
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,221 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,720 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 AND `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,627 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 OR `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,033 sec. */



-- 6.2. Indexes: (admission_year)
ALTER TABLE `student` ADD INDEX `idx_student_admission_year` (`admission_year` ASC);

SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,296 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,221 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 AND `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 35,834 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 OR `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 18,845 sec. */



-- 6.3. Indexes: (admission_year), (teach_time)
ALTER TABLE `student` ADD INDEX `idx_student_teach_time` (`teach_time` ASC);

SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,296 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,296 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 AND `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,718 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 OR `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 51,746 sec. */



-- 6.4. Indexes: (admission_year, teach_time)
ALTER TABLE `student` DROP INDEX `idx_student_admission_year`;
ALTER TABLE `student` DROP INDEX `idx_student_teach_time`;
ALTER TABLE `student` ADD INDEX `idx_student_admission_year_teach_time` (`admission_year` ASC, `teach_time` ASC);

SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,296 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,175 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 AND `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,031 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 OR `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 8,767 sec. */



-- 6.5. Indexes: (admission_year, teach_time), (teach_time)
ALTER TABLE `student` ADD INDEX `idx_student_teach_time` (`teach_time` ASC);

SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,297 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,297 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 AND `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,031 sec. */
SELECT SQL_NO_CACHE COUNT(`student_id`) FROM `student` WHERE `admission_year` = 2000 OR `teach_time` = 8;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 9,251 sec. */


-- 7. Bonus! Let's modify OR with UNION. Indexes: (admission_year), (teach_time)
SELECT SQL_NO_CACHE SUM(bb) FROM (
  (SELECT COUNT(`student_id`) AS bb FROM `student` WHERE `admission_year` = 2000)
  UNION
  (SELECT COUNT(`student_id`) AS bb FROM `student` WHERE `teach_time` = 8)
) t;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0,577 sec. */