免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 2527 | 回复: 4

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

论坛徽章:
0
发表于 2009-03-06 19:15 |显示全部楼层
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 编辑 ]

论坛徽章:
0
发表于 2009-03-06 19:48 |显示全部楼层
hi, try to add an index on column (clock)

论坛徽章:
0
发表于 2009-03-06 20:01 |显示全部楼层
原帖由 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)

论坛徽章:
0
发表于 2009-03-07 00:32 |显示全部楼层

回复

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

thx,

论坛徽章:
0
发表于 2009-03-08 01:08 |显示全部楼层
You should check whether there are dirty data which mysql doesn't know.
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP