Bug #57544 "Sending data" state split
Submitted: 19 Oct 2010 0:06
Reporter: Roel Van de Paar (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.0, 5.1 OS:Any
Assigned to: CPU Architecture:Any

[19 Oct 2010 0:06] Roel Van de Paar
Description:
Can the "sending data" state be split so it is clearer what is happening during this state?

==============
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table test2;
+-------+-------------------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------------------
| test2 | CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------
1 row in set (0.00 sec)

mysql> insert into test2 select * from test limit 1000000;
Query OK, 1000000 rows affected, 1 warning (15.40 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> show profile;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000167 |
| checking permissions |  0.000008 |
| Opening tables       |  0.000013 |
| System lock          |  0.000010 |
| Table lock           |  0.000010 |
| init                 |  0.000012 |
| optimizing           |  0.000003 |
| statistics           |  0.000009 |
| preparing            |  0.000006 |
| executing            |  0.000003 | <--------
| Sending data         | 15.383389 | <--------
| end                  |  0.000008 |
| query end            |  0.000004 |
| freeing items        |  0.013766 |
| logging slow query   |  0.000002 |
| logging slow query   |  0.000003 |
| cleaning up          |  0.000004 |
+----------------------+-----------+
17 rows in set (0.00 sec)
==============

The manual says:
Sending data: "The thread is processing rows for a SELECT statement and also is sending data to the client."
Executing: "The thread has begun executing a statement."

Note that in the example above there is 15.4 seconds of sending data, while there is almost no data returned to the client.

It would be good to have this "sending data" state split. Maybe this can be split out to:
#1 Reading from disk ("reading from disk")
#2 Deciding which records to include (actual SELECT) ("processing select" or "executing" - this seems more like "executing" to me than the definition above which maybe could be renamed to "initialization" or "preparing to execute")
#3 Actually sending data to the client ("sending data")

How to repeat:
N/A
[4 Feb 2011 2:59] Gary Pendergast
Another state worth splitting - "Freeing items". 

This state includes both freeing item objects, and invalidating the query cache. The former usually won't take much time, but the latter can. IT would be good to clarify what the query is doing if it gets stuck on this state.