MySql - slow sending data phase

Question

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?

Answer 1

Do you have indexes on this table? You said it returns about 300. But how many does it need to search through to find those 300?

Answer 2

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

Answer 3

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.