Simple count id in MySql table is taking to long

Question

I have to tables with 65.5 Million rows: 1)

CREATE TABLE RawData1 (
  cdasite varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  id int(20) NOT NULL DEFAULT '0',
  timedate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  type int(11) NOT NULL DEFAULT '0',
  status int(11) NOT NULL DEFAULT '0',
  branch_id int(20) DEFAULT NULL,
  branch_idString varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (id,cdasite,timedate),
  KEY idx_timedate (timedate,cdasite)
) ENGINE=InnoDB;

2) Same table with partition (call it RawData2)

PARTITION BY RANGE ( TO_DAYS(timedate))
(PARTITION p20140101 VALUES LESS THAN (735599) ENGINE = InnoDB,
 PARTITION p20140401 VALUES LESS THAN (735689) ENGINE = InnoDB,
 .
 .
 PARTITION p20201001 VALUES LESS THAN (738064) ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

I'm using the same query:

SELECT count(id) FROM RawData1
where timedate BETWEEN DATE_FORMAT(date_sub(now(),INTERVAL 2 YEAR),'%Y-%m-01') AND now();

2 problems: 1. why the partitioned table runs longer then the regular table? 2. the regular table returns 36380217 in 17.094 Sec. is it normal, all R&D leaders think it is not fast enough, it need to return in ~2 Sec.

What do I need to check / do / change ? Is it realistic to scan 35732495 rows and retrieve 36380217 in less then 3-4 sec?

Answer 1

For 2, your COUNT(id) may be checking each value of id in the range to see if it is NULL (the column is marked NOT NULL but I don't know if mySQL's query planner will take notice of this) which is an extra lookup operation because id isn't included in the timedate index. Try use COUNT(*) instead and see if that helps any.

For 1, you are partitioning by a function rather than a direct column, so it isn't going to be able to perform simple partition filtering when querying base on just the column value. Even if you partition based on the column value directly you should n't expect simple partitioning to produce faster results unless you are spreading the data over multiple devices (or even then) - partitioning is more of a management feature than a performance one. You need to provide more detail before we can help more there: what is the full definition of the partitioned table and its indexes? How much slower are you finding it?

For both, you should give the output of EXPLAIN <query> so we can see the query plan that is produced.

(edit the extra information into your question, rather than as comments in response to this answer, otherwise some people would might be able to help may not see it)