KILL QUERY_ID IN (SELECT QUERY_ID FROM information_schema.PROCESSLIST WHERE ...)
or
KILL THREAD_ID IN (SELECT id FROM information_schema.PROCESSLIST WHERE ...)
or
KILL USER IN (SELECT user FROM information_schema.PROCESSLIST WHERE ...)

Eric Bergen
eric.bergen@gmail.com
TiVo

If you're going to add support for killing queries in a specific state
then why not just add the ability to pass in a state? I can see some
use cases for killing queries in a specific state that are more useful
than sleep.

Off the top of my head:
Killing a query while it is waiting for table lock. This would be
especially handy for things like alter table or a big update where you
want to kill it only if it is waiting on a lock but before it has done
any modifications. This may be because it is holding the write lock
and causing other queries to pileup and you know the rollback will be
fast.

It may also be useful to be able to kill queries in opening tables
state in a load shedder to try to quiet down table cache trashing. Or
maybe killing queries in unauthenticated state that are holding up the
accept thread waiting on a bad dns request.

I'm sure there are more state specific kill commands that would come in handy.

static const char *thread_state_info(THD *tmp)
{
#ifndef EMBEDDED_LIBRARY
  if (tmp->net.reading_or_writing)
  {
    if (tmp->net.reading_or_writing == 2)
      return "Writing to net";
    else if (tmp->get_command() == COM_SLEEP)
      return "";
    else
      return "Reading from net";
  }
  else
#endif
  {
    if (tmp->proc_info)
      return tmp->proc_info;
    else if (tmp->mysys_var && tmp->mysys_var->current_cond)
      return "Waiting on cond";
    else
      return NULL;
  }
}

plugin/feedback/sender_thread.cc:  thd->proc_info="feedback";
sql/sql_table.cc:    thd_proc_info(thd, "Writing to binlog");
sql/sql_table.cc:    thd_proc_info(thd, 0);
sql/event_scheduler.cc:  thd->proc_info= "Clearing";
sql/event_scheduler.cc:  thd->proc_info= "Initialized";
sql/event_scheduler.cc:    new_thd->proc_info= "Clearing";
sql/event_scheduler.cc:    new_thd->proc_info= "Clearing";
sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Waiting for ndbcluster binlog update to "
sql/ha_ndbcluster_binlog.cc:  const char *proc_info= "<no info>";
sql/ha_ndbcluster_binlog.cc:    proc_info= injector_thd->proc_info;
sql/ha_ndbcluster_binlog.cc:                        "  injector proc_info: %s"
sql/ha_ndbcluster_binlog.cc:                        ,proc_info
sql/ha_ndbcluster_binlog.cc:  thd->proc_info= "Opening " NDB_REP_DB "." NDB_REP_TABLE;
sql/ha_ndbcluster_binlog.cc:  thd->proc_info= "Syncing ndb table schema operation and binlog";
sql/ha_ndbcluster_binlog.cc:    thd->proc_info= "Waiting for ndbcluster to start";
sql/ha_ndbcluster_binlog.cc:    thd->proc_info= "Waiting for first event from ndbcluster";
sql/ha_ndbcluster_binlog.cc:    thd->proc_info= "Waiting for event from ndbcluster";
sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Waiting for schema epoch";
sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Processing events from schema table";
sql/ha_ndbcluster_binlog.cc:      thd->proc_info= "Processing events";
sql/ha_ndbcluster_binlog.cc:          thd->proc_info= "Committing events to binlog";
sql/ha_ndbcluster_binlog.cc:  thd->proc_info= "Shutting down";
sql/sql_partition.cc:  thd->proc_info="end";
sql/sql_base.cc:                  thd->proc_info= "DBUG sleep";
sql/sql_load.cc:  thd_proc_info(thd, "reading file");
sql/sql_load.cc:    thd_proc_info(thd, "End bulk insert");
sql/slave.cc:  thd_proc_info(thd, "Loading slave GTID position from table");
sql/slave.cc:  thd->proc_info= messages[SLAVE_RECON_MSG_WAIT];
sql/slave.cc:  thd->proc_info = messages[SLAVE_RECON_MSG_AFTER];
sql/sp_head.cc:    thd_proc_info(thd, "closing tables");
sql/lock.cc:  thd_proc_info(thd, "Table lock");
sql/sql_show.cc:        thd_info->proc_info= (char*) (tmp->killed >= KILL_QUERY ?
sql/sql_partition_admin.cc:  thd_proc_info(thd, "verifying data with partition");
sql/sql_class.cc:    proc_info= msg;
sql/sql_class.cc:  proc_info="login";
sql/sql_parse.cc:  thd_proc_info(thd, "updating status");
sql/sql_select.cc:  thd_proc_info(thd, "show_explain_trap");
sql/sql_select.cc:  thd_proc_info(thd, save_proc_info);
sql/sql_select.cc:    thd_proc_info(thd, (!strcmp(save_proc_info,"Copying to tmp table") ?
sql/sql_select.cc:                  "Copying to tmp table on disk" : save_proc_info));
storage/csv/ha_tina.cc:  old_proc_info= thd_proc_info(thd, "Checking table");
storage/myisam/ha_myisam.cc:  thd_proc_info(thd, "Checking table");
storage/myisam/ha_myisam.cc:        thd_proc_info(thd, "Repair done"); // to reset proc_info, as
storage/myisam/ha_myisam.cc:        thd_proc_info(thd, "Repair by sorting");
storage/myisam/ha_myisam.cc:      thd_proc_info(thd, "Repair with keycache");
storage/myisam/ha_myisam.cc:      thd_proc_info(thd, "Sorting index");
storage/myisam/ha_myisam.cc:    thd_proc_info(thd, "Analyzing");
storage/myisam/ha_myisam.cc:  thd_proc_info(thd, "Saving state");
storage/myisam/ha_myisam.cc:    thd_proc_info(thd, "Creating index");
storage/pbxt/src/discover_xt.cc:  session->set_proc_info("creating table");
storage/pbxt/src/discover_xt.cc:  session->set_proc_info("After create");
storage/pbxt/src/discover_xt.cc:  thd_proc_info(thd, "creating table");
storage/pbxt/src/discover_xt.cc:  thd_proc_info(thd, "After create");
storage/spider/spd_trx.cc:  thd->proc_info = "";
storage/archive/ha_archive.cc:  old_proc_info= thd_proc_info(thd, "Checking table");
storage/maria/ha_maria.cc:  old_proc_info= thd_proc_info(thd, "Checking status");
storage/maria/ha_maria.cc:  thd_proc_info(thd, "Checking keys");
storage/maria/ha_maria.cc:  thd_proc_info(thd, "Checking data");
storage/maria/ha_maria.cc:  old_proc_info= thd_proc_info(thd, "Scanning");
storage/maria/ha_maria.cc:  old_proc_info= thd_proc_info(thd, "Checking table");
storage/maria/ha_maria.cc:        thd_proc_info(thd, "Repair done");
storage/maria/ha_maria.cc:        thd_proc_info(thd, "Repair by sorting");
storage/maria/ha_maria.cc:      thd_proc_info(thd, "Repair with keycache");
storage/maria/ha_maria.cc:      thd_proc_info(thd, "Sorting index");
storage/maria/ha_maria.cc:        thd_proc_info(thd, "Analyzing");
storage/maria/ha_maria.cc:  thd_proc_info(thd, "Saving state");
storage/maria/ha_maria.cc:    const char *save_proc_info= thd_proc_info(thd, "Creating index");