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!
