#!/bin/bash
linecount=0
#SUBSTRING=$(echo "SELECT DISTINCT `color_idx`.`value`, COUNT(DISTINCT color_idx.entity_id) AS");
processes=$(echo "show processlist" | mysql -hhost -Pport -uuser -ppassword)
oldIfs=$IFS

IFS='
'
echo "Checking for slow MySQL queries..."
for line in $processes
do
    if [ "$linecount" -gt 0 ]
        then
            pid=$(echo "$line" | cut -f1)
            length=$(echo "$line" | cut -f6)
            query=$(echo "$line" | cut -f8)
            #Id User    Host    db  Command Time    State   Info
            if [ "$length" -gt 200 ]
                then

                    #SELECT DISTINCT `color_idx`.`value`, COUNT(DISTINCT color_idx.entity_id) AS
                    
                    #ckeck qour query if you need or remove for kill all ling queries
                    if [[ $query =~ "SELECT DISTINCT" && $query =~ "COUNT(DISTINCT" && $query =~ "_idx.entity_id)"  ]];
                         then
                    
                        echo "WARNING:  Killing query with pid=$pid with total execution time of $length seconds! (query=$query)"
                        killoutput=$(echo "kill query $pid" |  mysql -hhost -Pport -uuser -ppassword)
                        echo "Result of killing $pid:  $killoutput"
                    fi
            fi
    fi
    linecount=`expr $linecount + 1`
done
IFS=$oldIfs