WL#1654: Speed up ALTER TABLE (part 2: Modify MyISAM handler to support fast CREATE/DROP INDEX)
Affects: Server-7.1 — Status: Assigned — Priority: Medium
Enable MyISAM to do much faster add/Drop indexes. This will be done by allowing adding or dropping of just one index without having to do a full alter table like we do today (now we rebuild all row entries and all index for a CREATE/DROP INDEX). Note: The interdependency between this task and WL#180 was put in place to help a customer track progress on BUG#2364. When WL#1563 and WL#1654 have been completed, the bug can be closed. -- Trudy Pelzer 2005-09-01
Add the following new functions to MyISAM: mi_drop_index(MI_INFO *info, ulonglong key_map) This will drop all the above index from MyISAM tables. The basic algorithm would be as follows: - For each active dropped index - Scan through the index in page order, moving all found pages to the free page list (that matches indexs block size, ie. info->s->state.key_del[keyinfo->block_size] ) - remove key and key segements from MYISAM_SHARE (moving the following index down). Also move down the key_root. - Remove the dropped index from the .MYI file, shifting all the later index up. Update the number of index and index parts in the header. - Update 'used_index' bit map. - Update MYISAM_SHARE->MI_BASE_INFO with number of keys In theory we could allow readaccess to the table while doing the drop index if we first updated all structures and the .MYI file and then started to add things to the free page list. ------------- int mi_add_index(MI_INFO, MI_KEYDEF *keydefs, uint index); The above would add a new index to the MyISAM file, at the given position. This will return an error if there is not enough place in the .MYI file to add an index (directory is full) or if there was some other error while creating the index. The basic algorithm would be as follows: - Add index to the .MYI file at the given position (shifting other information up) - Update base informaiton in the .MYI file for max_key_length etc (see mi_create.c for how this is calculated) - Add the index to MYISAM_SHARE to the right position for the key, moving up the following indexs. - Copy the create-one-index code from mi_check.cc in mi_repair() to create data for one index. In theory we could allow readaccess to the table while doing the add index if we first updated all structures and the .MYI file, then marked the new index as disabled while we created it. After creating the table we would then need to set the table version 0 to force a reopen of the table so that the new index would become visible.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.