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).

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
- 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.