Innodb Index Rebuilding

INNODB INDEX RE-BUILDING:

            In innodb, index re-building does not give more disk space. We can get more spaces in tablespace level. For index rebuilding, innodb cannot be done using optimize and repair command. It works only for MyISAM Tables.

            Here we cannot use mysqlcheck –optimize to re-build index. For innodb tables analyze table command will work. Instead of repair and optimize, we have to recreate the table.

Mysql> analyze table member_attributes;

+---------------------------+---------+----------+----------+

| Table                     | Op      | Msg_type | Msg_text |

+---------------------------+---------+----------+----------+

| everest.member_attributes | analyze | status   | OK       |

+---------------------------+---------+----------+----------+

1 row in set (0.00 sec)

mysql> repair table member_attributes;

+---------------------------+--------+----------+---------------------------------------------+

| Table                     | Op     | Msg_type | Msg_text                                                |

+---------------------------+--------+----------+---------------------------------------------+

| everest.member_attributes | repair | note     | The storage engine for the table doesn't support repair |

+---------------------------+--------+----------+---------------------------------------------+

1 row in set (0.01 sec)

mysql> optimize table member_attributes;

+---------------------------+----------+----------+-------------------------------------------+

| Table                     | Op       | Msg_type | Msg_text                                                          |

+---------------------------+----------+----------+-------------------------------------------+

| everest.member_attributes | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| everest.member_attributes | optimize | status   | OK                                                                |

+---------------------------+----------+----------+-------------------------------------------+

2 rows in set (0.15 sec)

ONLINE INDEX RE-BUILDING

            For innodb, we can do index re-building in online. To check out index length use the following query.

Before Rebuilding (Index length)

mysql> select index_length from information_schema.tables where table_schema='evereststage' and table_name='member_attributes';

+--------------+

| index_length |

+--------------+

|      1589248 |

+--------------+

1 row in set (0.09 sec)

mysql> delete from member_attributes;

Query OK, 30808 rows affected (1.13 sec)

            After deleting rows from table, now we can check index length from information_schema table. Here index are fragmented, so size cannot be reduced.

mysql> select index_length from information_schema.tables where table_schema='evereststage' and table_name='member_attributes';

+--------------+

| index_length |

+--------------+

|      1589248 |

+--------------+

1 row in set (0.00 sec)

After Rebuilding (Index Length)

            To re-build the index we need execute the following query. Only by alter table command we can re-build the index in online. Here we have to recreate the table once again by the following command.

Mysql> alter table member_attributes engine=innodb;

Query OK, 0 rows affected (0.07 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select index_length from information_schema.tables where table_schema='evereststage' and table_name='member_attributes';

+--------------+

| index_length |

+--------------+

|        16384 |

+--------------+