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: | |
Category: | MySQL Server | Severity: | S5 (Performance) |
Version: | mysql8.0.23 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[21 Jun 2021 15:56]
peng gao
[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!