mysql查询效率低，请问怎么破？

Question

现在有一张表A，数据440W。

在select count(1) from A where unix_timestamp(createdDate) > unix_timestamp('2013-10-01 00:00:00') AND unix_timestamp(createdDate) < unix_timestamp('2013-10-31 23:59:59');的时候耗时3.59s。当我与另外一张表关联查询时，COUNT时，耗时16s。

现在我这样对它分区。
先创建一个 A2表，表结构与A表一致。
然后为A2加上分区，并把timestamp类型的createdDate作为分区字段。
ALTER TABLE A2 PARTITION BY RANGE(UNIX_TIMESTAMP(createdDate))(
PARTITION p20120101 VALUES LESS THAN (UNIX_TIMESTAMP('2012-01-01 00:00:00')),
PARTITION p20130101 VALUES LESS THAN (UNIX_TIMESTAMP('2013-01-01 00:00:00')),
PARTITION p20140101 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')),
PARTITION p20150101 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
PARTITION p20160101 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
PARTITION p20170101 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00'))
);

然后
ALTER TABLE A RENAME TO A_BAK;
ALTER TABLE A2 RENAME TO A;
INSERT INTO A SELECT * FROM A_BAK;

最后在使用select count(1) from A where unix_timestamp(createdDate) > unix_timestamp('2013-10-01 00:00:00') AND unix_timestamp(createdDate) < unix_timestamp('2013-10-31 23:59:59');查询，发现还要3.59s。

请问我应该怎么破？怎么来提高查询效率。

我的mysql版本是5.5.28

Answer 1

标准做法是先用explain分析一下时间用到什么地方去了

unix_timestamp(createdDate)这种表达式貌似利用不到索引，而mysql又没有表达式索引

所以如果createDate是datetime一类的数据类型的话，不要用unix_timestamp()转换后比较，直接比较试试

Answer 2

虽然你分区是根据 UNIX_TIMESTAMP(createdDate) 在分区 单本质上还是对 createdDate 字段进行分区.
查询应与分区保持一样的数据,所以查询直接针对时间查询就行,不要转成unix时间戳.
select count(1) from A where createdDate > str_to_date('2013-10-01 00:00:00', '%Y-%m-%d %h:%i:%s') AND createdDate < str_to_date('2013-10-31 23:59:59', '%Y-%m-%d %h:%i:%s');

Answer 3

select xx from xxx where createdDate between 'xxx' and 'xxx'