1

I am currently deleting 9 million rows of a table with 11 million incrementally by 200,000 rows per run.

Will I need to manually rebuild the table and indexes or does this happen automatically after every delete?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user989990
  • 155
  • 1
  • 7
  • 14
  • I don't understand the question? Are you under the impression that by deleting rows, you're deleting the table itself? or are you concerned that your ID column will no longer be sequential (which is normal)? – deltree Mar 14 '12 at 22:58
  • I heard that deleting rows from innodb tables requires a table rebuild, does this happen automatically or do I need to do it manually? – user989990 Mar 14 '12 at 23:00

1 Answers1

1

Yes, you have to manually rebuild the table, you can do so like this:

ALTER TABLE database.table ENGINE=InnoDB;
Mike Purcell
  • 19,847
  • 10
  • 52
  • 89
  • I ran the command and it deleted all but 30,000 of the rows, Why would that happen? – user989990 Mar 14 '12 at 23:10
  • Is there anything in particular I should be looking for? It's a long list. – user989990 Mar 14 '12 at 23:16
  • If you are still deleting rows, I would wait until it's complete. Then re-run the alter and see where stand. – Mike Purcell Mar 14 '12 at 23:24
  • Ok. Just to clarify, are you trying to reclaim disk space, or are you saying that only 30K of the 9M rows were actually deleted? – Mike Purcell Mar 14 '12 at 23:31
  • I had 11 million rows, 9 million that I wanted deleted and 2 million that I needed.... I deleted the 9 million then ran alter and it deleted 1,970,000 of the rows that I needed. – user989990 Mar 14 '12 at 23:34
  • How did you delete the rows? If from the mysql command line, what was the count after the delete completed. – Mike Purcell Mar 14 '12 at 23:38
  • I deleted the rows that matched a certain criteria using phpmyadmin. After it was completed the table showed ~2 million rows. – user989990 Mar 14 '12 at 23:44
  • Ok, so the delete was successful, and you ran the alter which should have reclaimed some of the space. What is the issue now? – Mike Purcell Mar 14 '12 at 23:58
  • Before the alter I had the ~2 million rows I needed. After the alter I only have 37,000 rows. Not sure what caused the additional deletions. – user989990 Mar 15 '12 at 00:09
  • Could the additional deletions be a sign of a damaged table? – user989990 Mar 15 '12 at 00:11
  • Oh man, that sucks. But the alter wouldn't have deleted any rows from the table. If you do `select count(*) from table`, it really only has 37K rows? Are you sure you aren't looking at some other value through the phpmyadmin interface? – Mike Purcell Mar 15 '12 at 00:12
  • Check out this post: http://stackoverflow.com/questions/226172/how-do-i-repair-an-innodb-table – Mike Purcell Mar 15 '12 at 00:14
  • Yeah, 37k after alter. Sadly, I didn't have a complete backup. Ouch. – user989990 Mar 15 '12 at 00:27
  • Sorry dude, obviously that was not my intention. In fact I had to confirm with one of the system guys and they confirmed that we run that after a major purge of data on innodb tables. – Mike Purcell Mar 15 '12 at 00:29
  • 1
    No worries mate, this is just a reminder that I should maintain up-to-date backups. – user989990 Mar 15 '12 at 00:42
  • Im curious why the loss of rows on an alter though. Maybe google around and see if you find anything. – Mike Purcell Mar 15 '12 at 00:45
  • Did you try restarting the server after the alter? – Mike Purcell Mar 15 '12 at 20:02