mysql optimize very large table

Question

I have a MyIsam table about 15Gb large. I have deleted about a half of the rows from it and the delete query took about 10 hours. Now I'm afraid to start optimize table, because I don't know how long it's gonna take. If you have an experience with such large tables, can you please share with me.

PS After the deletion process the query "select * from table limit 0,30" takes years. Will optimize table help?

Will it take more than 10 hours?

What's gonna happen, if I interrupt the "optimize table" query?

Toupee

Answer 1

To optimize the table will help to reduce the size (index will be recalculated etc.) If you deleted half of the rows, you should optmize your table. In my company we have tables about 2-3 gb. Optmize doesn't take so much like delete.

If you want that your queries will be faster, optimize your table.

---

Another hint for deleting many many rows from a large table: You can do it without using any delete command.

Select the rows not to be deleted into an empty table that has the same structure as the original table:

INSERT INTO t_copy SELECT * FROM t WHERE ... ;

Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:

RENAME TABLE t TO t_old, t_copy TO t;

Drop the original table:

DROP TABLE t_old;

Sulfonate

Be aware that when using LOCK TABLES (for making sure nothing changes while running your operations), you can't use RENAME TABLE. You want ALTER TABLE instead, see https://mcmap.net/q/1625513/-renaming-a-locked-table. – Morehouse