47

One of my queries on MySQL 5.0.45 is running slow in "sending data" phase. The query is a simple select, returns about 300 integer ID fields as result set.

mysql> SELECT source_id FROM directions WHERE (destination_id = 10);
+-----------+
| source_id |
+-----------+
|         2 |
|         8 |
...
|      2563 |
+-----------+
341 rows in set (2.13 sec)

I am note sure why is "sending data" phase so slow and what can be done to make it fast. Please note I am executing this query on MySQL prompt on the server itself, so not really expecting it to spend so much time in "sending data". Any clues?

If it helps, I have 3 text fields on this table, but since they are not being selected, I am expecting they are not cause of this slowness.

This query runs thousands of times a day and can't really afford to spend 2 secs on it each time.

Profiling result:

mysql> show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000003 |
| checking query cache for query | 0.000051 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000011 |
| System lock                    | 0.000005 |
| Table lock                     | 0.000023 |
| init                           | 0.00002  |
| optimizing                     | 0.00001  |
| statistics                     | 0.00006  |
| preparing                      | 0.000014 |
| executing                      | 0.000005 |
| Sending data                   | 2.127019 |
| end                            | 0.000015 |
| query end                      | 0.000004 |
| storing result in query cache  | 0.000039 |
| freeing items                  | 0.000011 |
| closing tables                 | 0.000007 |
| logging slow query             | 0.000047 |
+--------------------------------+----------+
18 rows in set (0.00 sec)

UPDATE: I stumbled upon the following URL which says

Each time means the time elapsed between the previous event and the new event. So, the line:
| Sending data | 0.00016800 |
means that 0.00016800 seconds elapsed between "executing" and "Sending data". It is, it takes 0.00016800 seconds to execute the query.

http://forums.mysql.com/read.php?24,241461,242012#msg-242012

Can somebody validate?

Improve this question
5
  • 8
    The statement is not true on the linked page. If you do a SHOW PROCESSLIST while the query is running it will show that the query is indeed in the Sending data state. (Not in the previous one). - And please noone say that the processlist is showing the next step in the execution. :) Commented Jul 21, 2011 at 16:35
  • @vbence: well have you ever seen a query that takes more than a few us to run? The above theory makes sense. Commented Jan 30, 2015 at 19:03
  • @user9111337 Oh yes. Aggregate statements (GROUP BY) with WHERE caluse will usually result in selects that can't be solved by using an index (EXPLAIN ... will say "USING WHERE") - if the table is large enough this can take several minutes. - How is this reported? You can see it for yourself by trying the a SHOW PROCESSLIST. Commented Jan 31, 2015 at 21:22
  • 1
    POKE! can't believe this has no good answer Commented Jun 2, 2015 at 18:55
  • I think that this similar question should be helpful. stackoverflow.com/questions/10347193/… Commented Jan 14, 2016 at 6:27

9 Answers 9

Reset to default
31

An explain-plan is usually the best place to start whenever you have a slow query. To get one, run

DESCRIBE SELECT source_id FROM directions WHERE (destination_id = 10);

This will show you a table listing the steps required to execute your query. If you see a large value in the 'rows' column and NULL in the 'key' column, that indicates that your query having to scan a large number of rows to determine which ones to return.

In that case, adding an index on destination_id should dramatically speed your query, at some cost to insert and delete speed (since the index will also need to be updated).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

While I agree that an EXPLAIN/DESCRIBE query is the way to find out how to fix it, you didn't really describe what the "sending data" phase is for. See dev.mysql.com/doc/refman/5.0/en/general-thread-states.html
Thank you for your this answer. It helped me and I feel myself a bit wiser after this.
30

I had the same issue: Send Data was very slow, but I had the correct indexes etc.

After much digging around, I found that my join was comparing two fields that were indexed, but had different Collation - one was latin1_swedish_ci and the other was uft8_general_ci.

Once I sent them both to utf8 the query was significantly faster (from 2.7 seconds to 0.002 seconds)

Improve this answer

3 Comments

Hooray for this answer pointing us in the right direction!!! I couldn't believe it but it an on the fly table with a field used in an index was getting created with default collation of latin1_swedish_ci in our testing environment - lost almost an entire day trying to figure out why we were getting a complete stall on the "sending data" step for a query that took milliseconds to run and had only a few hundred results!!
It is how gems are hidden over the Internet. Thank you, Peter. I would suggest providing a quick fix like: SET NAMES 'utf8mb4'; for the session... it helps and then the database needs to be reconfigured to be consistent with char sets.
Also, found a solid solution to peg to character sets in section [mysqld] of my.cnf with the following statements: skip-character-set-client-handshake collation-server=utf8_unicode_ci character-set-server=utf8
9

You could probably look at hardware part of mysql server. As Mysql doc says:

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

So, if your server has slow disk I/O due to huge db/table file or disabled tableperfile InnoDB option/fragmentation/incorrectly configured RAID/disk crash process started (wait for soon disk death)/any other reason of disk I/O slowness - it could be reason for dramatically increased "Sending data" step as at this stage server gathering all requested data from disk and sends it to client.

Of course you should try to optimize select to use indexes first and make sure this is not programming issue as this affects this stage time in most cases.

Improve this answer

1 Comment

MySQL 8.0 docs says this state has been rolled into Executing.
3

Profiling is, in my opinion, useless. It has misleading categories like "sending data", which is no help.

SELECT source_id FROM directions
    WHERE (destination_id = 10);

would benefit from

INDEX(destination_id, source_id)

(in that order). Meanwhile, drop INDEX(destination_id) since this index handles such needs. This index is a "covering index".

Improve this answer

Comments

1

I experienced this after moving from MySQL 5.5.x to 5.7.x. My query using joins was fast on MySQL 5.5 and really slow on MySQL 5.7.

The problem was that MySQL 5.7 chose another set of indexes than MySQL 5.5 did.

Adding USE INDEX fixed the issue.

Improve this answer

Comments

1

I had two index (date_index and id) ,

i had WHERE date_index>NOW() - INTERVAL 24 HOURS and an ORDER BY id in query, MySql preferred id as index and it didn't use date_index that caused long query time for big tables.

i found it in a legacy system after 5 years that tables was grown.

Improve this answer

Comments

0

I had the same problem with slow "sending data". In my case there was a missing foreign key on a joined table that wasn't even used in the query. Adding the foreign key fixed it for me.

Improve this answer

Comments

0

According to MySQL 8 Doc:

Prior to MySQL 8.0.17: The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query. MySQL 8.0.17 and later: This state is no longer indicated separately, but rather is included in the Executing state.

Seems this state is a misnomer and should be under the Executing state.

Improve this answer

Comments

-1

Your query spends 2.127019 to execute the query. This is probably because you have a large amount of data, and your are missing an index on the destination_id column. Try :

CREATE INDEX index_destination_id ON directions (destination_id);

Then your request will run smoothly.

Improve this answer

2 Comments

This statement is FALSE see my comment on the question.
But for me, in a different scenario, in fact adding an index resolved my slow query - sometimes the obvious gets missed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.