When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
+----------------------+------------+
| starting | 0.023145 |
| checking permissions | 0.003454 |
| Opening tables | 0.000054 |
| System lock | 0.000056 |
| init | 0.000004 |
| Opening tables | 0.000726 |
| setup | 0.009575 |
| creating table | 0.005515 |
| After create | 0.000013 |
| System lock | 0.073562 |
| copy to tmp table | 999.999999 |
| rename result table | 1.218323 |
| end | 0.000026 |
| Opening tables | 0.000625 |
| System lock | 0.424206 |
| query end | 0.000010 |
| closing tables | 0.000004 |
| freeing items | 0.000095 |
| cleaning up | 0.000040 |
+----------------------+------------+
I have one primary key and one secondary key. So since i don't see the index being restored in the profile i assumed that the index is being restored along side the table copy. This is very far from optimal right ? Why hasn't this been handled in InnoDB ? Or am i missing something here. Please let me know. Thanks.
