Mysql – How to use subquery on the same table in MySQL

MySQLmysql-5.5performancequery-performancesubquery

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.

Best Answer

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.

ALTERNATIVE

Try gathering the keys you know need to be deleted and do the DELETE JOIN with it:

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;
INSERT INTO table1
    SELECT A.id FROM table1 A INNER JOIN table2 b
    ON  A.id = B.id
    AND a.field1 = b.field1
    AND a.field2 = b.field2
    AND a.field3 = b.field3
    AND b.id = ?
;
ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);
DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;
DROP TABLE DeleteIDs;

SUGGESTION

Maybe an index would help

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);
ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);

Give it a Try !!!

SQL Server Optimization – Why SQL Server Runs Subquery for Each Row

The slow plan isn't calculating the MAX for each row in the outer query.

In fact it never explicitly calculates it at all.

It gives a plan similar to

WITH CTE
     AS (SELECT TOP(1) WITH TIES *
         FROM   SubqueryTest
         WHERE year IS NOT NULL
         ORDER  BY year desc)
SELECT month,
       count(*)
FROM   CTE
GROUP  BY month

Slow Plan (Estimated Row Counts)

enter image description here

You have a non covering index on year asc so it scans that backwards to get the rows in the first year (shows as a seek because of the implicit IS NOT NULL predicate).

Unfortunately it doesn't seem to differentiate between TOP 1 and TOP 1 WITH TIES when estimating row counts.

In this case it makes a huge difference. (estimated 2 key lookup vs actual 4,424,803) so you get an inappropriate plan.

Slow Plan (Actual Row Counts)

enter image description here

You could consider adding month into the index on year either as a key or included column to make the index covering. The benefit of adding it as a secondary key column would be that it could then feed into a stream aggregate without an additional sort (though for only 12 distinct values a hash aggregate would be fine anyway).

A non covering index on such a non selective column is really pretty useless for the vast majority of queries. The index is totally ignored by the "fast" plan which ends up doing a parallel scan on the whole table and evaluating the predicate on all 27,445,400 rows (in preference to performing the huge number of lookups).

enter image description here

Best Answer

Now for your question...

OPTION #1

OPTION #2

CAVEAT

Related Solutions

Mysql – Delete from table where multiple fields match select subquery from other table

ALTERNATIVE

SUGGESTION

Give it a Try !!!

SQL Server Optimization – Why SQL Server Runs Subquery for Each Row

Related Question