日期型字段做索引最长遇到的问题就是时间重复，导致返回结果非预期数量

实验环境：MySQL, create_time是unixtime风格的日期型字段ctime TIMESTAMP型的字段。

首先未ctime创建索引

		
CREATE TABLE `members_tmp` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`member_id` INT(11) NOT NULL,
	`user_id` INT(11) NOT NULL DEFAULT '0',
	`fields` TEXT NOT NULL,
	`create_time` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `user_id` (`user_id`),
	INDEX `member_id` (`member_id`),
	INDEX `ctime` (`ctime`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=40168;
		
		

		
select count(id) as c from  members_tmp  where ctime > '2011-';
或
select count(id) as c from  members_tmp  where ctime like '201%';
		
		

删除索引 INDEX `ctime` (`ctime`) 再次查看你会发现结果有所变化

查看重复记录

		
explain select * from (select *,count(id) as c from  members_tmp  where ctime like '2012-%' group by ctime) as tmp order by c desc;
		
		

由于redhat与centosYUM源中尚未提供php-fpm所以我采用编译安装，网上有一些爱好者制作过php-fpm的rpm包。是否使用你自己那注意。