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

ulmer

Question:
How can I get "select from_unixtime(max(clock)) from history;"  (12 mins required!)
as quickly as "select max(itemid) from history;"

[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)

[ 本帖最后由 ulmer 于 2009-3-7 00:40 编辑 ]

yejr

hi, try to add an index on column (clock)

ulmer

原帖由 yejr 于 2009-3-6 19:48 发表
hi, try to add an index on column (clock)

index is already created:
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      |         |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

the primary key is ( itemid, clock)

ulmer

I have added second index specialy to the column 'clock'
and resolved this problem

thx,

yueliangdao0608

You should check whether there are dirty data which mysql doesn't know.