CODEWITHSUNDEEP
mysqlsqldatabase
Mellon
Mellon

Reputation: 38842

table optimization

I am using MySQl , I have a table named cars which is in my dev_db database.

I inserted about 6,000,000 data into the table (That's a large amount of data insertion) by using bulk insertion like following:

INSERT INTO cars (cid, name, msg, date) 
VALUES (1, 'blabla', 'blabla', '2001-01-08'),
       (11, 'blabla', 'blabla', '2001-11-28'), 
       ... ,
       (3, 'blabla', 'blabla', '2010-06-03');

After this large data insertion into my cars table

I decide to also optimize the table like following:

OPTIMIZE TABLE cars;

I waited 53min for the optimization, finally it is done and mysql console shows me the following message:

enter image description here

The Msg_text shows me this table does not support optimize... , which makes my brain yields two questions to ask :

1. Does the mysql message above means the 53min I waited actually did nothing useful??

2. is it necessary to optimize my table after large amount data insertion? and why?

Upvotes: 1

Views: 331

Answers (3)

Johan
Johan

Reputation: 76567

As you can read in the output InnoDB does not support optimize as such.
Instead it does a recreate + optimize on the indexes instead.

The result is much the same and should not really bother you, you end up with optimized indexes.

However you only ever have to optimize your indexes if you delete rows or update indexed fields.
If you only ever insert then your B-trees will not get unbalanced and do not need optimization.

So:

Does the mysql message above means the 53min I waited actually did nothing useful??

The time spend waiting was useless, but not for the reason you think.
If there is anything to optimize, MySQL will do it.

is it necessary to optimize my table after large amount data insertion? and why?

No, never.
The reason is that MySQL (InnoDB) uses B-trees, which are fast only if they are balanced.
If the nodes are all on one side of the tree, the index degrades into a ordered list, which gives you O(n) worst case time, An fully balanced tree has O(log n) time.
However the index can only become unbalanced if you delete rows, or alter the values of indexed fields.

Upvotes: 0

Fedir RYKHTIK
Fedir RYKHTIK

Reputation: 9974

It looks like, You have InnoDB table, which doesn't support OPTIMIZE TABLE

Upvotes: 1

Rene Pot
Rene Pot

Reputation: 24815

Optimize is useful if you have removed or overwritten rows, or if you have changed indexes. If you just inserted data it is not needed to optimize.

The MySQL Optimize Table command will effectively de-fragment a mysql table and is very useful for tables which are frequently updated and/or deleted.

Also look here: http://www.dbtuna.com/article.php?id=15

Upvotes: 1

Related Questions