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.