CODEWITHSUNDEEP
mysqlsql
user34537
user34537

Reputation:

vaccumm mysql? shrink file size

Because of a bug i had inserted a LOT of rows in one table. I deleetd the rows but my db is >1gb instead of being 200mb. How do i vaccumm it? I trid optimizing the table. It didnt shrink the size. Am i stuck with this huge mysql file?

Upvotes: 0

Views: 260

Answers (1)

SilverbackNet
SilverbackNet

Reputation: 2074

Create an identical table structure, select * into newtable from oldtable then drop oldtable then alter table newtable rename oldtable. The quickest cross-platform way.

(In mysql you can use rename table newtable to oldtable, but that's not standard sql.)

One caveat, this only works if mysql is set up to be one file per table, which is the default AFAIK, but it can also be set up to be all tables in one file. If it's set up that way, you'll have to clone the entire database, a much more significant undertaking. If you have a single "ibdata" file, you have all tables in one file, and you should probably see this answer.

Upvotes: 3

Related Questions