Monday, June 13, 2011

How to improve Mysql select count(*) performance

Today,select count(*) for a table,used time as following:

--first run:

select count(*) from mt_data;
+----------+
| count(*) |
+----------+
|  9750353 |
+----------+
1 row in set (1 min 14.72 sec)

--second run:
mysql> select count(*) from mt_data;
+----------+
| count(*) |
+----------+
|  9750353 |
+----------+
1 row in set (4.27 sec)


used time is too much, how to improve the select count(*) performance ?

first,show the index for this tables, the result as following:

Create Table: CREATE TABLE `mt_data` (
  `guid` varchar(36) NOT NULL,
  `orgid` int(9) NOT NULL,
  `tabid` int(9) NOT NULL,
  `name` varchar(128) NOT NULL,
  `IsDeleted` char(1) NOT NULL,
  `createtime` datetime NOT NULL,
  `modifytime` datetime NOT NULL,
  `value0` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`guid`,`orgid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

second, show the explain for this SQL,the result as following:





the explain display used primary key to count(*),why slow?

because the primary key is a cluster index, it includes index key and data,

so the data length is large,the primay key and second index physical strcture as following:

































we try to create a sencond index and test again.

mysql> create index ind_guid_mt_data on mt_data (guid);

--first  run
mysql> select count(*) from mt_data;
+----------+
| count(*) |
+----------+
|  9750353 |
+----------+
1 row in set (1.77 sec)

find the root casue,so the select count(*) performance improve.

No comments:

Post a Comment