CODEWITHSUNDEEP
mysqlperformance
user107068
user107068

Reputation:

MySql - slow sending data phase

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?

Upvotes: 47

Views: 86715

Answers (9)

Dean Or
Dean Or

Reputation: 2911

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.

Upvotes: 0

Wouter
Wouter

Reputation: 1353

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.

Upvotes: 0

Rick James
Rick James

Reputation: 142433

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".

Upvotes: 3

Ehsan Chavoshi
Ehsan Chavoshi

Reputation: 725

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.

Upvotes: 1

BQffen
BQffen

Reputation: 63

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.

Upvotes: 1

Peter Duley
Peter Duley

Reputation: 426

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)

Upvotes: 30

Ivan
Ivan

Reputation: 331

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.

Upvotes: 9

Arnaud Bienvenu
Arnaud Bienvenu

Reputation: 51

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.

Upvotes: -1

Brad B
Brad B

Reputation: 603

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).

Upvotes: 31

Related Questions