[MDEV-13301] Optimize DROP INDEX, ADD INDEX into RENAME INDEX Created: 2017-07-12  Updated: 2020-08-26  Resolved: 2019-04-03

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.2.2, 10.3.0
Fix Version/s: 10.4.4

Type: Bug Priority: Major
Reporter: Nicklas Laine Overgaard Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 4
Labels: instant

Attachments: File rename_index+varchar_length.patch    
Issue Links:
Blocks
is blocked by MDEV-17376 Server fails to set ADD_PK_INDEX, DRO... Closed
Duplicate
is duplicated by MDEV-18259 ASAN heap-use-after-free or server cr... Closed
Problem/Incident
causes MDEV-23356 InnoDB: Failing assertion: field->col... Closed
Relates
relates to MDEV-23571 InnoDB does not raise a warning if AL... Open
relates to MDEV-7318 RENAME INDEX Closed
relates to MDEV-11424 Instant ALTER TABLE of failure-free r... Closed
relates to MDEV-13134 Introduce ALTER TABLE attributes ALGO... Closed

 Description   

MDEV-7318 is requesting SQL syntax for ALTER TABLE…RENAME INDEX, similar to MySQL 5.7.

One could use DROP INDEX, ADD INDEX to simulate RENAME INDEX, but that will currently cause InnoDB to rebuild the index. The index rebuild should be optimized away, and only the index should be renamed. The InnoDB source code for renaming indexes exists starting with MariaDB 10.2, but it is disabled.

InnoDB should allow ALGORITHM=INSTANT operation for DROP INDEX, ADD INDEX when there is no actual data change. If an index is marked as corrupted, it would be nice to refuse ALGORITHM=INSTANT and actually rebuild the index.

 Comments   
Comment by Ivo Havener [ 2018-05-03 ]

This looks like a duplicate of MDEV-7318 - https://jira.mariadb.org/browse/MDEV-7318

And +1

Comment by Marko Mäkelä [ 2018-11-09 ]

I repurposed this ticket for a something similar that does not involve syntax changes.

Comment by Thirunarayanan Balathandayuthapani [ 2018-11-12 ]

I attached the patch which does rename for add index & drop index which contains same column and same prefix.
Patch which does it inside innodb. (Patch also contains indexed varchar column changes). I hope it could be useful.
rename_index+varchar_length.patch

It is based on 10.2

Comment by Marko Mäkelä [ 2018-12-04 ]

I would like to move the rename detection to the SQL layer, so that index renaming can be done instantly on a wider selection of storage engines.

Comment by Eugene Kosov (Inactive) [ 2018-12-05 ]

Patch changed. Seems to work.

Comment by Marko Mäkelä [ 2018-12-28 ]

serg, can you please review the changes outside InnoDB?

Comment by Eugene Kosov (Inactive) [ 2019-01-16 ]

Note for myself. After the fix for MDEV-17376 lands it'll be worth look at it and check how much impact it will have on this issue.

Comment by Eugene Kosov (Inactive) [ 2019-03-16 ]

PR rebased to 10.4

Generated at Thu Feb 08 08:04:30 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.