MySql - slow sending data phase

Question

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

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)

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.

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.

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.

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