[Resolved]Problem with select max(colname) from table in InnoDB: too slow - Chinaunix

[font=]
mysql> select from_unixtime(max(clock)) from history;
+---------------------------+
| from_unixtime(max(clock)) |
+---------------------------+
| 2009-03-05 13:52:46    |
+---------------------------+
1 row in set (12 min 12.93 sec)
mysql> select * from history limit 5;
+--------+------------+---------------+
| itemid | clock    | value       |
+--------+------------+---------------+
|  39158 | 1235024984 |       0.0000 |
|  38040 | 1235024984 | 50085888.0000 |
|  25277 | 1235024984 | 10236012.0000 |
|  35539 | 1235024984 |       0.0000 |
|  36699 | 1235024984 |       0.0000 |
+--------+------------+---------------+
5 rows in set (0.00 sec)
-- total rows: 100033983

mysql> explain select count(*) from history;
+----+-------------+---------+-------+---------------+-----------+---------+------+-----------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows    | Extra    |
+----+-------------+---------+-------+---------------+-----------+---------+------+-----------+-------------+
|  1 | SIMPLE    | history | index | NULL       | history_1 | 12    | NULL | 100033983 | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

mysql> show index from history;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| history |       1 | history_1 |          1 | itemid    | A       |       17 |    NULL | NULL |    | BTREE    |       |
| history |       1 | history_1 |          2 | clock    | A       | 100033983 |    NULL | NULL |    | BTREE    |       |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.19 sec)

mysql> select min(itemid) from history;
+-------------+
| min(itemid) |
+-------------+
|    17591 |
+-------------+
1 row in set (0.02 sec)

mysql> select max(itemid) from history;
+-------------+
| max(itemid) |
+-------------+
|    42460 |
+-------------+
1 row in set (0.02 sec)