Menu
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?
658
Remove any unnecessary indexes on the table, paying particular attention to UNIQUE indexes as these disable change buffering. Don't use a UNIQUE index unless you need it; instead, employ a regular INDEX. Take a look at your slow query log every week or two. Pick the slowest three queries and optimize those.
Millions of rows is fine, tens of millions of rows is fine - provided you've got an even remotely decent server, i.e. a few Gbs of RAM, plenty disk space. You will need to learn about indexes for fast retrieval, but in terms of MySQL being able to handle it, no problem. Save this answer. Show activity on this post.
When your database is large consider having a DDL (Data Definition Language) for your database table in MySQL/MariaDB. Adding a primary or unique key for your table requires a table rebuild. Changing a column data type also requires a table rebuild as the algorithm applicable to be applied is only ALGORITHM=COPY.
You are using a MyISAM table and the space required for the table exceeds what is permitted by the internal pointer size. MyISAM permits data and index files to grow up to 256TB by default, but this limit can be changed up to the maximum permissible size of 65,536TB (2567 − 1 bytes).
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;
86
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
