List:InnoDB Storage Engine« Previous MessageNext Message »
From:Shashank Raj Holavanalli Date:April 26 2013 1:51am
Subject:InnoDB OPTIMIZE TABLE Query
View as plain text  
In MySQL 5.6.11 I observed that the OPTIMIZE TABLE query does not have the
restoring secondary index status although my table has a primary and a
secondary key. Here is the query profile i got when I ran the query on my
InnoDB table:

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

Looking at this i assume that InnoDB is creating the secondary index as and
when it copies the records to temporary table by traversing the primary key
clustered index. So the secondary index is created with unsorted data which
is far from optimal. So why didn't InnoDB do this instead

drop secondary keys
copy table
add secondary keys

Can anyone please explain ?

Thanks.


-- 

Thank you,

Shashank Raj Holavanalli

Person Number: 50027381

Thread
InnoDB OPTIMIZE TABLE QueryShashank Raj Holavanalli26 Apr