Solution: SELECT Query with LAST_INSERT_ID()

The query is so evil/slow because it does a Full Index Scan (FIS). Other call this operation also a Index Fast Full Scan (IFFS). This is much too much work for the result wanted (the value of the AUTO_INCREMENT column).

This can be shown with the Query Execution Plan you get with the EXPLAIN command:

sql> EXPLAIN SELECT LAST_INSERT_ID() FROM test;
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|    1 | SIMPLE      | test  | index | NULL          | PRIMARY | 4       | NULL | 1048577 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+

sql> EXPLAIN FORMAT=JSON SELECT LAST_INSERT_ID() FROM test;
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "test",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["id"],
      "rows": 1048577,
      "filtered": 100,
      "using_index": true
    }
  }
}

The correct SELECT query looks as follows:

sql> SELECT LAST_INSERT_ID();

sql> EXPLAIN SELECT LAST_INSERT_ID();
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

sql> EXPLAIN FORMAT=JSON SELECT LAST_INSERT_ID();
{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "No tables used"
    }
  }
}

The MariaDB ANALYZE command provides some additional information:

mariadb> ANALYZE FORMAT=JSON SELECT LAST_INSERT_ID() FROM test;
{
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 846.52,
    "table": {
      "table_name": "test",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["id"],
      "r_loops": 1,
      "rows": 4174842,
      "r_rows": 4.19e6,
      "r_total_time_ms": 597.33,
      "filtered": 100,
      "r_filtered": 100,
      "using_index": true
    }
  }
}