select MAX() from MySQL view (2x INNER JOIN) is slow

Question

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:

1. snapshot_data: 5213
2. snapshot_booking: 4113837
3. booking_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)

Answer 1

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.

Answer 2

Since it sounds like snapshot_booking is a many:many relation between the other two tables, there is no real need for PRIMARY KEY(Magic_id). Get rid of it and replace it by the composite index (snapshot_nummer, action_link) that Gerard suggested.

If necessary, also have a regular INDEX going the other way: (action_link, snapshot_nummer). If you have any queries that look at bookingState, then let that decide between the two orders -- have the PK make it more efficient to that column, then set up an INDEX for the opposite.

INT is 4 bytes and has a large range. If the values stored are not that big, consider shrinking the size to, for example, SMALLINT UNSIGNED (2 bytes, range: 0..65K). Etc.

Is everything really NULLable? Change some things to NOT NULL.

Why does snapshot_data exist? It is just a mapping from an id to a datetime? Why not use the datetime in the other table(s) instead of the id??