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 |
| 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.
Best Answer
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