3

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)
  • Why do you bother optimizing the "MAX from a view" query when you have 3 others that give you the same result - and fast? – ypercubeᵀᴹ Feb 14 '18 at 22:39
  • An external program is using the slow MAX query by default. I am just wondering if it's possible to fix it in the database. – matt Feb 14 '18 at 22:41
2

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.

  • Thank you for your advise! The query is now faster (4,64 sec). I did not deleted the index. I think MySQL deleted it automatically. (The index was created automatically because Snapshot_Nummer is a foreign key. stackoverflow.com/a/1145183/4468096) – matt Feb 15 '18 at 23:42
0

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??

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.