MySql - slow sending data phase

Admin 1
Viewed 78475

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?

mysql performance
7 Answers

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

user_1057010

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

user_17668310

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.

user_17168450

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.

user_5305530
Related