DELETE command not completing on 30,000,000 row table I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. Before I add any new, more optimized indexes, I converted the table from MyISAM to InnoDB and am looking to delete a lot of the rows that contain junk data.

The database is MySQL 5.0 and I have root access to the server. I was first running these commands through Adminer and then phpMyAdmin, both with the same results.

The command I am running is,

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'


Essentially, delete anything in this column that begins with a dash -.

It runs for about 3-5 minutes and then when I view the process list, it's gone.

I then run,

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'


and it returns millions of rows.

Why is my delete statement not completing?

PS, I am aware of how out-of-date MySQL 5.0 is. I am working on moving the DB to MySQL 5.6 w InnoDB (maybe MariaDB 10 w XtraDB) but until that happens, I am looking to answer this with the DB as is.

--

Edit removed, see my answer.
Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko)



The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:


Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread


In your case, reason #1 would occupy one rollback segment along with some of the undo space since you are deleting rows. Those rows must sit in ibdata1 until the delete is finished. That space is logically discarded but the diskspace does not shrink back.

You need to kill that delete right now. Once you kill the delete query, it will rollback the deleted rows.

You do this instead:

CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;


You could have done this against the MyISAM version of the table first. Then, convert it to InnoDB.