If you are using MySQL 5.1+, you can create a scheduled event in MySQL or a cronjob to poll the processlist from the information schema.

What queries do you look for in the processlist? Any DB Connection that ...

• runs longer that 30 seconds
• not used for replication
• runs a SELECT query

Here is that query:

SELECT id FROM information_schema.processlist
WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

Here is a Stored Procedure to collect those IDs and terminate the query using the KILL command:

CREATE DATABASE myutil;
USE myutil
DELIMITER $$ 
DROP PROCEDURE IF EXISTS `Kill_Long_Queries` $$ 
CREATE PROCEDURE `Kill_Long_Queries` () 
BEGIN

    CREATE TABLE IF NOT EXISTS IDs_To_Kill;
    (ndx INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL) ENGINE=MyISAM;
    TRUNCATE TABLE IDs_To_Kill;

    INSERT INTO IDs_To_Kill (ProcessID)
    SELECT id FROM information_schema.processlist
    WHERE time > 30 and user <> 'system user' and LEFT(info,6)='SELECT';

    SET @ndxnext = 0;
    SELECT MAX(ndx) INTO @ndxlast FROM IDs_To_Kill;
    WHILE @ndxnext < @ndxlast DO
        SET @ndxnext = @ndxnext + 1;
        SELECT id INTO @ProcessID from IDs_To_Kill WHERE ndx = @ndxnext;
        SET @SQL = CONCAT('KILL QUERY ',@ProcessID);
        PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;
    END WHILE;

END $$ 
DELIMITER ; 

If you want to go beyond terminating the query and just terminate the DB Connection, replace

SET @SQL = CONCAT('KILL QUERY ',@ProcessID);

with this

SET @SQL = CONCAT('KILL ',@ProcessID);

Launch Stored Procedure Using MySQL Event

USE myutil
DELIMITER $$
CREATE EVENT kill_query_event
    ON SCHEDULE EVERY 1 MINUTE
    STARTS (NOW() + INTERVAL 1 MINUTE + INTERVAL SECOND(NOW()) SECOND) DO
BEGIN
    CALL Kill_Long_Queries();
END $$ 
DELIMITER ; 

Launch Stored Procedure Using cron

In the OS, set up the crontab to go off every minute as follows

0 * * * * mysql -u... -p... -Dmyutil -ANe"CALL Kill_Long_Queries()"

CAVEAT for MySQL 5.0 Users

Since there are no events in the MySQL 5.0 and prior, you will have to do something like this:

for PROC_TO_KILL in `mysql -h... -u... -p... -ANe"SHOW PROCESSLIST" | grep -v "system user" | awk '{print $1}'` ; do mysql -h... -u... -p... -ANe"KILL QUERY ${PROC_TO_KILL}" ; done

SPECIAL CAVEAT

Without a MySQL Event, you would have to connect to mysql each time and poll the processlist. In the event that there are too many connections or if the mysqld starts blocking connection due to connect errors, your only hope is to write a perl script whose sole job is to kill long running query and using only one live session at the same time.

I wrote a perl script two years to run flush hosts every 10 minutes (How do you tell which host is close to being blocked in MySQL?)

Here is that script

#!/usr/bin/perl

use DBI;
use DBD::mysql;

my ($SLEEP_TIME_INTERVAL,$HEARTBEAT_CYCLE) = @ARGV;

$check_replication = 1;

$username = "whateveruser";
$password = "whateverpassword";

$MYSQL_HEARTBEAT_FILE = "/tmp/MySQLHeartBeat.txt";
$MYSQL_FLATLINE_FILE  = "/tmp/MySQLFlatLine.txt";

$MYSQL_PROCESSLIST_FILE = "/tmp/MySQL_Processlist.txt";

if ( $SLEEP_TIME_INTERVAL == 0 )
{
        open(fh_output,"> $MYSQL_FLATLINE_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
        printf fh_output "DONE !!!\n";
        close fh_output;
        exit 0;
}

$db = DBI->connect("DBI:mysql:information_schema;host=127.0.0.1",$username,$password)
or die "Cannot connect to the DB information_schema\n",$DBI->errstr(),"\n";

$st_globalstatus = $db->prepare("SHOW GLOBAL STATUS") or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";
$st_flush_hosts  = $db->prepare("FLUSH HOSTS")        or die "Cannot Prepare SQL Statement\n",$DBI->errstr(),"\n";

$heartbeat_count = 0;
$my_heart_is_beating = 1;
while ( $my_heart_is_beating )
{
        $st_globalstatus->execute() or die "Cannot Execute SQL Statement\n",$DBI->errstr(),"\n";
        {
                $StatusCount  = 0;
                while ( my $row = $st_globalstatus->fetchrow_hashref() )
                {
                        my $uptime = $row->{Value};
                }
                $st_globalstatus->finish();
                open(fh_output,"> $MYSQL_HEARTBEAT_FILE") or die "Can't Find File $MYSQL_HEARTBEAT_FILE\n$!\n";
                printf fh_output "%s : %s\n",$uptime;
                close fh_output;
        }
        for ($i = 0; $i < $SLEEP_TIME_INTERVAL; $i++)
        {
                if ( -f $MYSQL_FLATLINE_FILE )
                {
                        unlink $MYSQL_HEARTBEAT_FILE;
                        unlink $MYSQL_FLATLINE_FILE;
                        $my_heart_is_beating = 0;
                }
                sleep 1;
        }
        $heartbeat_count++;
        if ( $heartbeat_count == $HEARTBEAT_CYCLE )
        {
                $heartbeat_count = 0;
                $st_flush_hosts->execute();
        }
}

$db->disconnect();

I am not a perl expert, but I suggest that you alter this script to collect and kill the process IDs. Save it to a file called killquery.pl. All you need to do is launch this once from the command line as a background process.

nohup perl killquery.pl 5 30 &

This makes the perl script heartbeat every 5 sec, and run the Stored Procedure every 30 second

Give it a Try !!!

Of course it is the subqueries' fault. For each row of table main, this query goes through the whole transactions table twice (once for each subquery). That's a lot of load.

How about a simple join? This should have the same results and take much less than 50% of your processing time even with no indexes.

SELECT main.BAL,main.NAME,SUM(transaction.DR),SUM(transaction.CR)
FROM main
LEFT JOIN transaction on transaction.TNAME = main.NAME and transaction.DATE < '2000-05-01' 
GROUP BY main.MARK,main.NAME WITH ROLLUP