Why do our queries get stuck on the state "Writing to net" in MySql?

Question

We have a lot of queries

select * from tbl_message

that get stuck on the state "Writing to net". The table has 98k rows.

The thing is... we aren't even executing any query like that from our application, so I guess the question is:

• What might be generating the query?
• ...and why does it get stuck on the state "writing to net"

I feel stupid asking this question, but I'm 99,99% sure that our application is not executing a query like that to our database... we are however executing a couple of querys to that table using WHERE statement:

 SELECT Count(*) as StrCount FROM tbl_message WHERE m_to=1960412 AND m_restid=948

 SELECT Count(m_id) AS NrUnreadMail FROM tbl_message WHERE m_to=2019422 AND m_restid=440 AND m_read=1

 SELECT * FROM tbl_message WHERE m_to=2036390 AND m_restid=994 ORDER BY m_id DESC

I have searched our application several times for select * from tbl_message but haven't found anything... But still our query-log on our mysql server is full of Select * from tbl_message queries

Answer 1

Since applications don't magically generate queries as they like, I think that it's rather likely that there's a misstake somewhere in your application that's causing this. Here's a few suggestions that you can use to track it down. I'm guessing that your using PHP, since your using MySQL, so I'll use that for my examples.

Try adding comments in front of all your queries in the application, like this:

$sqlSelect  = "/* file.php, class::method() */";
$sqlSelect .= "SELECT * FROM foo ";
$sqlSelect .= "WHERE criteria";

The comment will show up in your query log. If you're using some kind database api wrapper, you could potentially add these messages automatically:

function query($sql)
{
    $backtrace = debug_backtrace();
    // The function that executed the query
    $prev = $backtrace[1];
    $newSql = sprintf("/* %s */ ", $prev["function"]);
    $newSql .= $sql;

    mysql_query($newSql) or handle_error();
}

In case you're not using a wrapper, but rather executing the queries directly, you could use the runkit extension and the function runkit_function_rename to rename mysql_query (or whatever you're using) and intercept the queries.