mysql taking too long to send data

I have a simple table with million of records (14,000,000) and for a simple query it is spending too much time "sending data".

The table

CREATE TABLE IF NOT EXISTS details (
  id int(11) NOT NULL,
  date date NOT NULL,
  time int(2) NOT NULL,
  minutes_online decimal(5,0) NOT NULL,
  minutes_playing decimal(5,0) NOT NULL,
  minutes_chatting decimal(5,0) NOT NULL,
  minutes_away decimal(5,0) NOT NULL
  PRIMARY KEY (id,date,time)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

The simple query

mysql> SELECT * FROM details WHERE id = 3014595;

Explain

mysql> EXPLAIN SELECT * FROM details WHERE id = 3014595;
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | details   | ref  | PRIMARY       | PRIMARY | 4       | const | 1482 |       |
+----+-------------+-----------+------+---------------+---------+---------+-------+------+-------+

Profile for the query

```
mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000024 |
| checking query cache for query | 0.000078 |
| checking permissions | 0.000014 |
| Opening tables | 0.000126 |
| System lock | 0.000011 |
| Table lock | 0.000030 |
| init | 0.000027 |
| optimizing | 0.000117 |
| statistics | 0.040077 |
| preparing | 0.000029 |
| executing | 0.000006 |
| Sending data | 7.536960 |
| end | 0.000013 |
| query end | 0.000004 |
| f

For anyone who stumble upon this question and wondering, even without upgrade of RAM, why sending data was taking so much longer. It is because sending data actually include time of searching the data that is to be sent.

https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html


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.

StackExchange Database Administrators Q#20545, answer score: 2