mysql 慢速,MySQL-慢速发送数据阶段

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.

Can somebody validate?

解决方案

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