クソデカ・テーブル

[mirakui]

mysql> show tables;
+------------------------+
| Tables_in_isutrain     |
+------------------------+
| distance_fare_master   |
| fare_master            |
| reservations           |
| seat_master            |
| seat_reservations      |
| station_master         |
| train_master           |
| train_timetable_master |
| users                  |
+------------------------+
9 rows in set (0.00 sec)

mysql> select count(1) from distance_fare_master;
+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from fare_master;
+----------+
| count(1) |
+----------+
|       81 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from seat_reservations;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from station_master;
+----------+
| count(1) |
+----------+
|       82 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from train_master;
+----------+
| count(1) |
+----------+
|    70272 |
+----------+
1 row in set (0.02 sec)

mysql> select count(1) from train_timetable_master;
+----------+
| count(1) |
+----------+
|  2807913 |
+----------+
1 row in set (0.21 sec)

mysql> select count(1) from users;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

[mirakui]

CREATE TABLE `train_timetable_master` (
  `date` date NOT NULL,
  `train_class` varchar(100) NOT NULL,
  `train_name` varchar(100) NOT NULL,
  `station` varchar(100) NOT NULL,
  `departure` time NOT NULL,
  `arrival` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

index なし

[mirakui]

0.1 sec くらい

SELECT
          `sr`.`reservation_id`,
          `sr`.`car_number`,
          `sr`.`seat_row`,
          `sr`.`seat_column`
        FROM
          `seat_reservations` `sr`,
          `reservations` `r`,
          `seat_master` `s`,
          `station_master` `std`,
          `station_master` `sta`
        WHERE
          `r`.`reservation_id` = `sr`.`reservation_id` AND
          `s`.`train_class` = `r`.`train_class` AND
          `s`.`car_number` = `sr`.`car_number` AND
          `s`.`seat_column` = `sr`.`seat_column` AND
          `s`.`seat_row` = `sr`.`seat_row` AND
          `std`.`name` = `r`.`departure` AND
          `sta`.`name` = `r`.`arrival`
AND ((`std`.`id` <= '43' AND '43' < `sta`.`id`) OR (`std`.`id` <= '55' AND '55' < `sta`.`id`) OR (`sta`.`id` < '43' AND '55' < `std`.`id`));