SELECT 180 records took 2 seconds My table is simple:


CREATE TABLE `od_form_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `formid` int(11) DEFAULT NULL,
  `items` mediumtext,
  `template` mediumtext,
  `draftid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=420 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT


And it only contains 180 rows:


mysql&gt; select count(*) from od_form_items;
+----------+
| count(*) |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)


The longest string is just 26314 bytes:


mysql&gt; select length(items) as a from qiban.od_form_items order by a desc limit 1;
+-------+
| a     |
+-------+
| 26314 |
+-------+
1 rows in set (0.01 sec)
mysql&gt; select length(template) as a from qiban.od_form_items order by a desc limit 1;
+------+
| a    |
+------+
| 6556 |
+------+
1 rows in set (0.01 sec)


But in mysql slow_log, it took over 2 seconds to select all records:


mysql&gt; select start_time, query_time, sql_text from mysql.slow_log order by start_time desc limit 10;
+---------------------+------------+--------------------------------------------+
| start_time          | query_time | sql_text                                   |
+---------------------+------------+--------------------------------------------+
| 2017-11-24 10:34:40 | 00:00:02   | SELECT * FROM `od_form_items`              +---------------------+------------+--------------------------------------------+
1 rows in set (0.01 sec)


How should I optimize it? What mysql parameter should I check? Thanks!
On an ordinary spinning drive, you can get about 100 IOPs.  If all 180 rows needed to reach off-row to get items and none needed for template, then that is 180 + miscellany to get SELECT * 180 times.  That's about 2 seconds.

SSD drives would be faster.

Compressing, in the client, those two columns, then storing them in BLOBs, would shrink things a bunch.  This would probably lead to fewer IOPs.

It seems like populating memcached would be a one-time action, so the 2 seconds would not be a big deal?