Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query like this which takes a really long time to run. The table is around 4 million rows.

DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;

I'm hoping I could create an index for (value_was, value_now) so I could do something like

DELETE FROM TABLE WHERE 
ID1 IN (SELECT ID1 from TABLE where value_was IS NULL) 
OR ID2 IN (SELECT ID2 FROM TABLE WHERE value_was <= value_now);

This table doesn't have primary key. It has two composite keys. And I guess I cannot use the same table in subquery, but how do I improve the performance of the first query?

Thanks very much any suggestion would be much appreciated.

share|improve this question
2  
Posting the output of SHOW CREATE TABLE table_name; might be helpful, as might the output of EXPLAIN SELECT * FROM table_name WHERE value_was IS NULL OR value_was <= value_now; Also, the (value_was,value_now) index should still improve performance of the first query, depending on what other indexes are already there... have you tried that? –  Michael - sqlbot May 15 '13 at 5:25
1  
In general you can use the same table in a sub-query. It's just that MySQL has a (stupid IMHO) restriction which prevents you of doing that in UPDATE or DELETE statements. –  a_horse_with_no_name May 15 '13 at 10:22
add comment

3 Answers

up vote 0 down vote accepted

Unfortunately, MySQL is quite notorious for subquery issues, particularly with non-SELECTs queries. I once addressed this back on Feb 22, 2011 : Problem with MySQL subquery

In that old post I found documentation on how MySQL handles subqueries. I have tried to keep up with this because I last edited my post on Oct 20, 2012 with the latest documentation.

In brief, subquery optimization may have rows disappear intermittently for the sake of getting the EXPLAIN plan figured out. You can read the documentation and learn about it if you can your wrap head around it (not going through that again).

Now for your question...

The most sensible approach, no JOINs, no sub-SELECTs, is the following:

DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;

Will you look at that. It's your query from the question. Why suggest your original idea ?

It is a full table scan IN ONE PASS. Any other approach can potentially double the work (or triple it if you try to get indexes involved this late in the game). Running it this way also delays the need to defragment the table.

If you want to delete and defragment, here are two options.

OPTION #1

MyISAM

DELETE FROM `TABLE` WHERE value_was IS NULL OR value_was <= value_now;
ALTER TABLE `TABLE` ENGINE=MyISAM;

InnoDB

DELETE FROM `TABLE` WHERE value_was IS NULL OR value_was <= value_now;
ALTER TABLE `TABLE` ENGINE=InnoDB;

OPTION #2

DELETE FROM `TABLE` WHERE value_was IS NULL OR value_was <= value_now;
CREATE TABLE `NEWTABLE`
SELECT * FROM `TABLE`
WHERE NOT (value_was IS NULL OR value_was <= value_now);
DROP TABLE `TABLE`;
ALTER TABLE `NEWTABLE` RENAME `TABLE`;

CAVEAT

Before you do anything, run this count

SELECT COUNT(1) INTO @Count_All FROM `TABLE`;
SELECT COUNT(1) INTO @Count_Zap FROM `TABLE`
WHERE value_was IS NULL OR value_was <= value_now;
SET @DeletePct = @Count_Zap * 100 / @Count_All;
SELECT @DeletePct;

@DeletePct is the Percentage of the Table that will be deleted if you run the DELETE.

If the Percentage is too low for you, then DELETE FROMTABLEWHERE value_was IS NULL OR value_was <= value_now; is all you need. Defragmentation can wait. Otherwise, you may choose one of the options or live with the table's row fragmentation.

On a side note, if you wish employ the use of indexes, please so after defragmenting the table.

share|improve this answer
add comment

You could try to use EXISTS clause instead of IN, since its usually less performance expensive:

  DELETE FROM TABLE T_delete
  WHERE EXISTS (SELECT 1
                FROM TABLE t_join
                WHERE value_was IS NULL
                AND T_delete.ID1 = t_join.ID1) 
  OR EXISTS (SELECT 1
                FROM TABLE t_join
                WHERE value_was <= value_now
                AND T_delete.ID2 = t_join.ID2);

Not sure if it would improve the performance, but you could also try to split this query into two instead of using a OR.

There's more info about optimizing queries with EXISTS here.

share|improve this answer
1  
Are you sure that will work with MySQL? Last time I checked it didn't allow sub-queries on the same table for DELETE and UPDATE statements. Not sure if this includes co-related subqueries though. –  a_horse_with_no_name May 15 '13 at 10:22
    
You can view it in this simplified sqlfiddle. edit: ops, you're right, I wrote an unvalid example and indeed it doesn't work. –  listik May 15 '13 at 15:08
add comment

You asked for suggestions. Here's one.

If I were trying to analyze this case, the first thing I would do is separate it out into two separate DELETE statements, one to cover the IS NULL case, and the other to cover the value_was < Value_now case. See whether they both run slow. I expect the IS NULL case to do a table scan, unless indexes work differently than what I'm used to. That's going to be slow.

Whether the other case can be sped up by having the appropriate index in place would be the major focus of the investigation.

share|improve this answer
    
The IS NULL check can use indexes. –  ypercube Jun 14 '13 at 12:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.