How do I use subquery on the same table in MySQL? 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 &lt;= 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 &lt;= 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.
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 &lt;= 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.