quicker execution of DELETE queries on large tables

dougmcc1

Delete queries take a long time to run and I'm looking for ways to speed them up without having to delete or move data.
I usually delete rows where the date field is equal to a particular date. So my query looks like this:

DELETE FROM my_table WHERE date='2007-04-20'

My tables generally have from 800,00 to 1,200,000 rows and it takes about 20 minutes for this query to run even though the date field is indexed. Does that sound slow? Is there anything else I can do to speed it up?

Thanks.

bradgrafelman

Well... you could always make sure that it's using the index... run a query like this:

EXPLAIN SELECT 1 FROM my_table WHERE date='2007-04-20'

What does it output?

dougmcc1

1 SIMPLE my_table ref date date 3 const 10531 Using index

Roger_Ramjet

How many other columns are indexed on that table?
Is the table being used by others at the same time?
Do you optimise the table after running a delete?

These are just some of the things that affect query speed.

Oh yes, and what table type is it?

dougmcc1

28/28 fields are indexed. There are three indexes that have more than one field. One of these does include the date field. Date is also indexed by itself separately.

No other users are using it at the same time.

I dont optimize the table after running a delete. I'm not familiar with that option.

The table type is MyIsam.

Roger_Ramjet

OK, every time you delete a row, all indexes have to be updated - so it is the 28 indexes that are slowing this down so much.
from the manual:
"The time required to delete individual rows is exactly proportional to the number of indexes. To delete rows more quickly, you can increase the size of the key cache by increasing the key_buffer_size system variable."

Now there is no absolute rule to this but with some types of index it is better to drop the index, do the delete and then recreate the index. This is particularly the case with FULL TEXT SEARCH indexes, as the manual tells us.

When rows are deleted the space they occupied in the disk file can be reused by rows inserted later, the same applies for indexes. This results in data that is badly ordered and requires a lot of disk seeks. Using OPTIMISE TABLE after a delete will reorder the table and indexes to prevent this problem. So after any major delete operation it is best to optimise the table and get rid of all the wasted space that slows things down.

Of course, the reordered data will be in PRIMARY KEY order which may still force a lot of disk seeks when querying on another column. If primary key is an autoinc then date inserted columns will be in the same order naturally - so it all depends on what this date column in your query is.

A fast efficient method for deleting a lot of rows using another index is to select the primary keys of the required rows into a work table and then delete by joining to that table. Rows will be deleted in primary key order which should be the most disk seek efficient.

Sxooter

dougmcc1 wrote:
28/28 fields are indexed. There are three indexes that have more than one field. One of these does include the date field. Date is also indexed by itself separately.

I dont optimize the table after running a delete. I'm not familiar with that option.

Wowsers, that's a lot of indexes. Are they all used? My guess is that the real cost is in updating all those indexes, not the delete itself.

Try an experiment, make a copy of that table, but with only the index on the date field, and try the same delete. See how long that takes, that will let you know how long the index updates are eating up.

You need to become familiar with optimize table. seriously.

dougmcc1

You need to become familiar with optimize table

Yeah I'm running that query right now. It's been running for 30 minutes. I can't do that after every delete query. We'll see how much of an impact it has.

I'm going to hold off on copying the table for now but after the optimize table query is finished I'll try deleting some of these indexes that I dont think are being used and we'll see what that does as well.

I'll look up key_buffer_size too.

Any other suggestions are welcome. Thanks.

Roger_Ramjet

Optimise is taking a long time because of all those indexes - they all have to be updated with the new record locations. Better to drop them, optimise then recreate them.

As I said, you may find it is quicker to: drop the indexes - do the delete- optimise the table - create the indexes.
Or my second suggestion using a work table with the primary keys - I have found that to be the quickest when working in Access.

Only benchmarking will tell you what is the most efficient method to use in your particular case.

Sxooter

The fact that MySQL's myisam tables store their indexes in the same file as the table itself is both a blessing and a curse. Generally, for lightly indexed tables, this is a good thing. For heavily indexes tables, it seems to be a bad thing.

And generally speaking indexing everything in sight is NOT a good strategy.