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&gt; SELECT * FROM details WHERE id = 3014595;


Explain

mysql&gt; 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&gt; 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 |
| freeing items                  | 0.000037 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+


As you can see, the SELECT statement used the index and read only 1482 rows. Yet, the query spent 7.536960 seconds sending the data. It is like the query read much more rows it needed.

It is a simple query, with just 7 fields (row avg 59 Bytes) and no fancy function. Any idea what can be causing this?

Note: id is the user ID. Each user may have at least one entry for every hour of every day. Therefore, id is not unique.

Edit: I have another table with the same structure and much more rows (34 Million). If I run the same query on this larger table, it returns the results in less than 1 second.

The only difference is that the larger table does not get as many queries as the smaller table.


Is it possible that the number of queries is slowing down the process? MySQL cache is on. I've also CakePHP caching the queries to reduce the number of queries.
Is it possible that the file where the table is saved is corrupted or something?


Update
The issue was solved by separating the data tier from the web tier. The data tier also got an upgrade on the RAM and is running on raid10.
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.