I want to optimize my MySQL view v_booking. At the moment I am wondering about the following issue:
My queries
Query 1 is slow (47,48 sec):
SELECT MAX(Snapshot_Nummer) FROM v_booking;
Query 2 is fast (0,04 sec):
SELECT MAX(Snapshot_Nummer) FROM snapshot_data;
Query 3 is fast (0,03 sec):
SELECT MAX(Snapshot_Nummer) FROM snapshot_booking;
Query 4 is fast (0,03 sec):
SELECT Snapshot_Nummer FROM v_booking ORDER BY Snapshot_Nummer DESC LIMIT 1;
In my case, all 4 queries are delivering the same result.
My question
Is there any way to execute the MAX() function fast and directly by using the MySQL view?
COUNT(*) of the 3 tables:
snapshot_data: 5213snapshot_booking: 4113837booking_data: 1484
CREATE TABLE
CREATE TABLE `snapshot_data` ( `Snapshot_Nummer` int(11) NOT NULL AUTO_INCREMENT, `Snapshot_Zeitpunkt` datetime DEFAULT NULL, PRIMARY KEY (`Snapshot_Nummer`) ) ENGINE=InnoDB AUTO_INCREMENT=5214 DEFAULT CHARSET=utf8
CREATE TABLE `snapshot_booking` ( `Magic_PK` int(11) NOT NULL AUTO_INCREMENT, `Snapshot_Nummer` int(11) NOT NULL, `Action_Link` int(11) NOT NULL, `bookingState` int(11) DEFAULT '0', PRIMARY KEY (`Magic_PK`), KEY `Snapshot_Nummer` (`Snapshot_Nummer`), KEY `Action_Link` (`Action_Link`), CONSTRAINT `snapshot_booking_ibfk_1` FOREIGN KEY (`Snapshot_Nummer`) REFERENCES `snapshot_data` (`Snapshot_Nummer`), CONSTRAINT `snapshot_booking_ibfk_2` FOREIGN KEY (`Action_Link`) REFERENCES `booking_data` (`Action_Link`) ) ENGINE=InnoDB AUTO_INCREMENT=4113838 DEFAULT CHARSET=utf8
CREATE TABLE `booking_data` ( `Action_Link` int(11) NOT NULL, `FaMaId` int(11) DEFAULT NULL, `BuchId` int(11) DEFAULT NULL, `MadaId` int(11) DEFAULT NULL, `StationId` int(11) DEFAULT NULL, `BOId` int(11) DEFAULT NULL, `BuchungCode` int(11) DEFAULT NULL, `DatumVon` datetime DEFAULT NULL, `DatumBis` datetime DEFAULT NULL, `BenutztVon` datetime DEFAULT NULL, `BenutztBis` datetime DEFAULT NULL, `BenutztKM` int(11) DEFAULT NULL, `StornoKZ` tinyint(1) DEFAULT NULL, `VorgaengerBuchId` int(11) DEFAULT NULL, `AngelegtDatum` datetime DEFAULT NULL, `AutostornoDatum` datetime DEFAULT NULL, `AenderungDatumKunde` datetime DEFAULT NULL, `WunschId` int(11) DEFAULT NULL, `WagenId` int(11) DEFAULT NULL, `Fix` tinyint(1) DEFAULT NULL, `FBNr` int(11) DEFAULT NULL, PRIMARY KEY (`Action_Link`), KEY `storno_index` (`StornoKZ`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE VIEW
CREATE
ALGORITHM = UNDEFINED
DEFINER = `car`@`%`
SQL SECURITY DEFINER
VIEW `v_booking` AS
SELECT
`booking_data`.`FaMaId` AS `FaMaId`,
`booking_data`.`BuchId` AS `BuchId`,
`booking_data`.`MadaId` AS `MadaId`,
`booking_data`.`StationId` AS `StationId`,
`booking_data`.`BOId` AS `BOId`,
`booking_data`.`BuchungCode` AS `BuchungCode`,
`booking_data`.`DatumVon` AS `DatumVon`,
`booking_data`.`DatumBis` AS `DatumBis`,
`booking_data`.`BenutztVon` AS `BenutztVon`,
`booking_data`.`BenutztBis` AS `BenutztBis`,
`booking_data`.`BenutztKM` AS `BenutztKM`,
`booking_data`.`StornoKZ` AS `StornoKZ`,
`booking_data`.`VorgaengerBuchId` AS `VorgaengerBuchId`,
`booking_data`.`AngelegtDatum` AS `AngelegtDatum`,
`booking_data`.`AutostornoDatum` AS `AutostornoDatum`,
`booking_data`.`AenderungDatumKunde` AS `AenderungDatumKunde`,
`booking_data`.`WunschId` AS `WunschId`,
`snapshot_data`.`Snapshot_Nummer` AS `Snapshot_Nummer`,
`snapshot_data`.`Snapshot_Zeitpunkt` AS `Snapshot_Zeitpunkt`
FROM
((`snapshot_booking`
JOIN `booking_data` ON ((`snapshot_booking`.`Action_Link` = `booking_data`.`Action_Link`)))
JOIN `snapshot_data` ON ((`snapshot_booking`.`Snapshot_Nummer` = `snapshot_data`.`Snapshot_Nummer`)))
EXPLAIN SELECT
Query 1
mysql> EXPLAIN SELECT MAX(Snapshot_Nummer) FROM v_booking; +----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+ | 1 | SIMPLE | snapshot_data | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 5213 | 100.00 | Using index | | 1 | SIMPLE | snapshot_booking | NULL | ref | Snapshot_Nummer,Action_Link | Snapshot_Nummer | 4 | carsharing.snapshot_data.Snapshot_Nummer | 797 | 100.00 | NULL | | 1 | SIMPLE | booking_data | NULL | eq_ref | PRIMARY | PRIMARY | 4 | carsharing.snapshot_booking.Action_Link | 1 | 100.00 | Using index | +----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+ 3 rows in set, 1 warning (0,04 sec)
Query 2
mysql> EXPLAIN SELECT MAX(Snapshot_Nummer) FROM snapshot_data; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0,03 sec)
Query 3
mysql> EXPLAIN SELECT MAX(Snapshot_Nummer) FROM snapshot_booking; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0,04 sec)
Query 4
mysql> EXPLAIN SELECT Snapshot_Nummer FROM v_booking ORDER BY Snapshot_Nummer DESC LIMIT 1; +----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+ | 1 | SIMPLE | snapshot_data | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 100.00 | Using index | | 1 | SIMPLE | snapshot_booking | NULL | ref | Snapshot_Nummer,Action_Link | Snapshot_Nummer | 4 | carsharing.snapshot_data.Snapshot_Nummer | 797 | 100.00 | NULL | | 1 | SIMPLE | booking_data | NULL | eq_ref | PRIMARY | PRIMARY | 4 | carsharing.snapshot_booking.Action_Link | 1 | 100.00 | Using index | +----+-------------+------------------+------------+--------+-----------------------------+-----------------+---------+------------------------------------------+------+----------+-------------+ 3 rows in set, 1 warning (0,04 sec)
INDEXES
mysql> SHOW INDEXES FROM snapshot_data; +---------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | snapshot_data | 0 | PRIMARY | 1 | Snapshot_Nummer | A | 5213 | NULL | NULL | | BTREE | | | +---------------+------------+----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0,04 sec)
mysql> SHOW INDEXES FROM snapshot_booking; +------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | snapshot_booking | 0 | PRIMARY | 1 | Magic_PK | A | 3959571 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | Snapshot_Nummer | 1 | Snapshot_Nummer | A | 4969 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | Action_Link | 1 | Action_Link | A | 1405 | NULL | NULL | | BTREE | | | +------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0,03 sec)
mysql> SHOW INDEXES FROM booking_data; +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | booking_data | 0 | PRIMARY | 1 | Action_Link | A | 1484 | NULL | NULL | | BTREE | | | | booking_data | 1 | storno_index | 1 | StornoKZ | A | 1 | NULL | NULL | YES | BTREE | | | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0,03 sec)
Thank you very much!
Update 1
Advise from Gerard H. Pille:
mysql> show indexes from snapshot_booking; +------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | snapshot_booking | 0 | PRIMARY | 1 | Magic_PK | A | 3959571 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | Snapshot_Nummer | 1 | Snapshot_Nummer | A | 4969 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | Action_Link | 1 | Action_Link | A | 1405 | NULL | NULL | | BTREE | | | +------------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0,03 sec) mysql> ALTER TABLE snapshot_booking ADD INDEX snapshot_nummer_action_link_index(Snapshot_Nummer, Action_Link); Query OK, 0 rows affected (7,83 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show indexes from snapshot_booking; +------------------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | snapshot_booking | 0 | PRIMARY | 1 | Magic_PK | A | 3959571 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | Action_Link | 1 | Action_Link | A | 1405 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | snapshot_nummer_action_link_index | 1 | Snapshot_Nummer | A | 5363 | NULL | NULL | | BTREE | | | | snapshot_booking | 1 | snapshot_nummer_action_link_index | 2 | Action_Link | A | 3960314 | NULL | NULL | | BTREE | | | +------------------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0,04 sec) mysql> EXPLAIN SELECT MAX(Snapshot_Nummer) FROM v_booking; +----+-------------+------------------+------------+--------+-----------------------------------------------+-----------------------------------+---------+------------------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------------+------------+--------+-----------------------------------------------+-----------------------------------+---------+------------------------------------------+------+----------+-------------+ | 1 | SIMPLE | snapshot_data | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 5213 | 100.00 | Using index | | 1 | SIMPLE | snapshot_booking | NULL | ref | Action_Link,snapshot_nummer_action_link_index | snapshot_nummer_action_link_index | 4 | carsharing.snapshot_data.Snapshot_Nummer | 738 | 100.00 | Using index | | 1 | SIMPLE | booking_data | NULL | eq_ref | PRIMARY | PRIMARY | 4 | carsharing.snapshot_booking.Action_Link | 1 | 100.00 | Using index | +----+-------------+------------------+------------+--------+-----------------------------------------------+-----------------------------------+---------+------------------------------------------+------+----------+-------------+ 3 rows in set, 1 warning (0,06 sec) mysql> SELECT MAX(Snapshot_Nummer) FROM v_booking; +----------------------+ | MAX(Snapshot_Nummer) | +----------------------+ | 5213 | +----------------------+ 1 row in set (4,64 sec)
Best Answer
Create a combined index on snapshot_booking (snapshot_nummer, action_link). Drop the index on snapshot_booking.snapshot_nummer. This will avoid reading the snapshot_booking table, reading the index will be sufficient.