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

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: 5213

• snapshot_booking: 4113837

• 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 NUL

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.

StackExchange Database Administrators Q#197955, answer score: 2