Bug #104076 Temp key field's length limit is small
Submitted: 21 Jun 2021 15:56 Modified: 1 Jul 2021 12:00
Reporter: peng gao Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:mysql8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[21 Jun 2021 15:56] peng gao
Description:
Hi all:

recently,we have sql not create autokey.we find autokey is large than MI_MAX_KEY_LENGTH.
so not create.

in 8.0.23 TABLE::add_tmp_key funcation

/*
        Check if possible key is too long, ignore it if so.
        The reason to use MI_MAX_KEY_LENGTH (myisam's default) is that it is
        smaller than MAX_KEY_LENGTH (heap's default) and it's unknown whether
        myisam or heap will be used for tmp table.
      */
      tkp.init_from_field(*reg_field);
      key_len += tkp.store_length;
      if (key_len > MI_MAX_KEY_LENGTH) { 
        return false;
      }

Here code comment,if temp key field's length large than  MI_MAX_KEY_LENGTH
will not create autokey in temp table.

But in our documentation(8.0.23) :

Starting with MySQL 8.0.16, the server always uses the InnoDB storage engine for managing internal
temporary tables on disk.

can use key_len > MAX_KEY_LENGTH here?

thanks!!

How to repeat:

mysql> show create table testauto;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testauto | CREATE TABLE `testauto` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc select * from (select max(id) id ,name from testauto group by name ) a ,(select max(id) id ,name from testauto group by name ) b  where a.id=b.id and a.name=b.name;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                       |
|  1 | PRIMARY     | <derived3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (hash join) |
|  3 | DERIVED     | testauto   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary                            |
|  2 | DERIVED     | testauto   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary                            |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

mysql> alter table testauto modify  name varchar(50);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc select * from (select max(id) id ,name from testauto group by name ) a ,(select max(id) id ,name from testauto group by name ) b  where a.id=b.id and a.name=b.name;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref         | rows | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL        | NULL    | NULL        |    2 |   100.00 | Using where     |
|  1 | PRIMARY     | <derived3> | NULL       | ref  | <auto_key0>   | <auto_key0> | 158     | a.id,a.name |    2 |   100.00 | Using index     |
|  3 | DERIVED     | testauto   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL        |    2 |   100.00 | Using temporary |
|  2 | DERIVED     | testauto   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL        |    2 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)
[28 Jun 2021 14:04] MySQL Verification Team
Hi Mr. gao,

Thank you for your bug report.

Regarding your question, no user can not create longer index lengths with those tables as well.

Regarding comments, it is not an important comment to be added to our source, since with that number of comments, our source would be overbearing.

Regarding documentation, we do not understand, what is it that we should document ???/
[28 Jun 2021 14:35] peng gao
This issue is in internal Temporary auto key. if derived table have no auto key,nest loop is slow. like this:

mysql> show create table testauto;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testauto | CREATE TABLE `testauto` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc select * from (select max(id) id ,name from testauto group by name ) a ,(select max(id) id ,name from testauto group by name ) b  where a.id=b.id and a.name=b.name;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                       |
|  1 | PRIMARY     | <derived3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (hash join) |
|  3 | DERIVED     | testauto   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary                            |
|  2 | DERIVED     | testauto   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary                            |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

In MySQL 8.0 Reference Manual have:
Storage Engine for On-Disk Internal Temporary Tables
Starting with MySQL 8.0.16, the server always uses the InnoDB storage engine for managing internal
temporary tables on disk.

I mean MI_MAX_KEY_LENGTH is myisam's default index limit.But Reference Manual indicate internal temporary tables now is innodb engine only.
so use MAX_KEY_LENGTH(3072U) is better here? auto key may create frequently.

It is just my own opinion.Sorry, i not good at engish.
[30 Jun 2021 11:13] MySQL Verification Team
Hi,

Thank you for your answers.

However, indices are unnecessary when entire table is scanned, like in most cases where temp. table is required.

Also, there are options to switch to other types of temporary tables.

Hence, this is not a bug.
[1 Jul 2021 12:00] peng gao
thanks!