mysql> explain SELECT count(*) FROM `fdp`.`reuters_rkd_pm`;
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | reuters_rkd_pm | index | NULL | PRIMARY | 53 | NULL | 7483719 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> SELECT count(*) FROM `fdp`.`reuters_rkd_pm`;
+----------+
| count(*) |
+----------+
| 7985412 |
+----------+
1 row in set ( 11.70 sec)
mysql> desc `fdp`.`reuters_rkd_pm`;
+------------------------+---------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+-------------------+-------+
| xref | int(10) unsigned | NO | PRI | NULL | |
| prod_dt | datetime | NO | | NULL | |
| event_cd | varchar(20) | NO | | NULL | |
| event_asof | datetime | NO | | NULL | |
| periodseries_startdate | datetime | NO | PRI | NULL | |
| periodseries_enddate | datetime | YES | | NULL | |
| periodseries_seqno | tinyint(4) | NO | PRI | NULL | |
| fyearend | char(6) | NO | PRI | NULL | |
| fperiodend | char(6) | NO | PRI | NULL | |
| periodtype | char(1) | NO | PRI | NULL | |
| periodnum | tinyint(3) unsigned | NO | PRI | 0 | |
| period_length | tinyint(3) unsigned | NO | | NULL | |
| period_length_unit | char(1) | NO | | NULL | |
| period_advance_date | datetime | YES | | NULL | |
| period_expect_date | datetime | YES | | NULL | |
| period_data_status | char(1) | YES | | NULL | |
| period_market_phase | tinyint(3) unsigned | YES | | NULL | |
| upd_stmp | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------------------+---------------------+------+-----+-------------------+-------+
18 rows in set (0.00 sec)
mysql>
表的记录所占据的磁盘空间在mysql administrator的Catalogs里面显示为1.7G,explain了都用了index,但是count(*)竟然需要12秒左右,如何优化?
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | reuters_rkd_pm | index | NULL | PRIMARY | 53 | NULL | 7483719 | Using index |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)
mysql> SELECT count(*) FROM `fdp`.`reuters_rkd_pm`;
+----------+
| count(*) |
+----------+
| 7985412 |
+----------+
1 row in set ( 11.70 sec)
mysql> desc `fdp`.`reuters_rkd_pm`;
+------------------------+---------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+-------------------+-------+
| xref | int(10) unsigned | NO | PRI | NULL | |
| prod_dt | datetime | NO | | NULL | |
| event_cd | varchar(20) | NO | | NULL | |
| event_asof | datetime | NO | | NULL | |
| periodseries_startdate | datetime | NO | PRI | NULL | |
| periodseries_enddate | datetime | YES | | NULL | |
| periodseries_seqno | tinyint(4) | NO | PRI | NULL | |
| fyearend | char(6) | NO | PRI | NULL | |
| fperiodend | char(6) | NO | PRI | NULL | |
| periodtype | char(1) | NO | PRI | NULL | |
| periodnum | tinyint(3) unsigned | NO | PRI | 0 | |
| period_length | tinyint(3) unsigned | NO | | NULL | |
| period_length_unit | char(1) | NO | | NULL | |
| period_advance_date | datetime | YES | | NULL | |
| period_expect_date | datetime | YES | | NULL | |
| period_data_status | char(1) | YES | | NULL | |
| period_market_phase | tinyint(3) unsigned | YES | | NULL | |
| upd_stmp | timestamp | NO | | CURRENT_TIMESTAMP | |
+------------------------+---------------------+------+-----+-------------------+-------+
18 rows in set (0.00 sec)
mysql>
表的记录所占据的磁盘空间在mysql administrator的Catalogs里面显示为1.7G,explain了都用了index,但是count(*)竟然需要12秒左右,如何优化?
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25267658/viewspace-693867/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25267658/viewspace-693867/