How do I use subquery on the same table in MySQL?

Question

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.

score 0 · Answer 1 · answered May 15 '13 at 07:33

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.

score 0 · Answer 2 · answered May 15 '13 at 10:07

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.

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

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.

How do I use subquery on the same table in MySQL?

3 Answers3

Now for your question...

OPTION #1

OPTION #2

CAVEAT