Fwd: [TS] Killed SQL-Task 1418007 on db-server thyme

[Mashiah Davidson]

Optimizations to the following query could be the most hot task for now:

DELETE FROM l
            WHERE l_from IN ( <some set> );

Table l is huge (say 250 mln links) and defined as

CREATE TABLE l (
l_to int(8) unsigned NOT NULL default '0',
l_from int(8) unsigned NOT NULL default '0',
PRIMARY KEY (l_to,l_from)
) ENGINE=MyISAM;

The <some set> is of about 160 000 records, which corresponds to
somewhat close to million records to be deleted from l.

Optimization is required for reducing single query execution time.
With increased execution time for a query the probablility for that
query to be killed increases.

mashiah

---------- Forwarded message ----------
From: <no-r...@toolserver.org>
Date: Sun, Jun 24, 2012 at 9:42 PM
Subject: [TS] Killed SQL-Task 1418007 on db-server thyme
To: mas...@toolserver.org


Hello mashiah,
a MySQL-query of yours was killed because you didn't mark it as
SLOW_OK and it have run for 498 seconds which was longer than allowed.
You can find the query below. Please have also a look at [1] to find
information how you can avoid killings of your queries. Maybe you can
optimze the query too?
The replication lag at kill-time was 134s.

Sincerly,
Query-Killer.
This eMail was sent automaticaly, please don't reply.

DELETE FROM l
                WHERE l_from IN
                      (
                       SELECT id
                              FROM isolated
                              WHERE cat= NAME_CONST('curcatuid',36232087) and
                                    act>=0
                      )


[1] https://wiki.toolserver.org/view/Database_access#Slow_queries_and_the_query_killer