delete query takes HOURS

sneakyimp

why on EARTH would this query:

DELETE FROM `test_zip_assoc` WHERE id > 100000

take an entire day when there are 10 million records in this database:

+--------------+------------------+------+-----+---------------+----------------+
| Field        | Type             | Null | Key | Default       | Extra          |
+--------------+------------------+------+-----+---------------+----------------+
| id           | int(12) unsigned |      | PRI | NULL          | auto_increment |
| f_id         | int(12) unsigned |      |     | 0             |                |
| zip          | varchar(5)       |      |     |               |                |
| lat_radians  | decimal(12,11)   |      | MUL | 0.00000000000 |                |
| long_radians | decimal(12,11)   |      | MUL | 0.00000000000 |                |
+--------------+------------------+------+-----+---------------+----------------+

Piranha

Is id used as FK in other tables? If they are, are they set to ON CASCADE DELETE? And do they have other FK that are set the same way? If it is a big database and lots and lots of tables that have rows that need to be deleted it may be quite slow. But a whole day? It sounds strange.

MarkR

How many rows are you deleting?

Maybe there is a locking problem- is some other process holding many locks on the table?

If you're going to regularly delete a large number of rows from a table, consider loading the data you want to keep into another table, then renaming that new table instead.

Another option would be to drop the indexes before the delete, then recreate them after it.

Deleting a lot of rows could be quite slow because it needs to reorganise all the indexes. However, it still shouldn't take half a day 🙂

Mark

sneakyimp

f_id doesn't point to anything at all. it just contains random numbers.

the table is of type MyISAM. i understand for these tables that locking is not record-by-record but locks the entire table, correct?

there should be no other locks on the table because this is my development server. when i query SHOW PROCESSLIST i get the delete query and my show processlist query and no other queries at all.

I was deleting 9 million records. it's also really slow when i try to delete 100,000 records from a 1 million record DB.

Roger_Ramjet

If you are going to delete 10% of a large table it is better to select into a new table then drop and rename. Your table and indexes will be more compact and well organised. It might even be quicker to do than the delete, but it will certainly be quicker to use afterwards.

sneakyimp

that's been my workaround. I suppose I was just wondering why deleting wasn't better optimized.

Roger_Ramjet

Page thrashing caused by poorly organised indexes, compounded by very fragmanted and disjointed data. Sequential table scan would probably be quicker, in which case drop the indexes before you do the delete and create them afterwards.