> 20150201<_ctime_<20150202
> primary key (ctime, cid, ctype)
Those imply that a small range will be fast and a long range will be slow. The rows to be fetched from the data is proportional to the time range. Partitioning will have no impact. Defragmenting will have little or no impact.
The .ibd file is where all the data an indexes are stored, so it should grow as you add rows. There are several forms of "overhead" and "fragmentation", etc, in that file. Most forms are unavoidable.
SHOW TABLE STATUS gives you a metric on Data_length and Index_length. The PRIMARY KEY is 'clustered' with the data, so it does not show up as part of Index_length.
select sum(test1),test1,sum(test2),test2 from mytable where ctime>=20150511 and ctime<20150512 group by ctime sort by ctime;
Is `ctime` effectively an number representing a date? And it is a BIGINT? The datatype DATE takes 3 bytes; BIGINT takes 8 bytes. This would be a big savings. But let's discuss the details of how to convert.
Let's discuss the other datatypes. "cid char(20)" takes 20 bytes or 60 bytes, depending on the CHARACTER SET. (It would help if you can get "SHOW CREATE TABLE mytable".) Is `cid` really fixed length? VARCHAR is better if it is not fixed length. (No, defragmenting is not important here.)
And do you really need BIGINT for the other fields? INT is 4 bytes, SMALLINT is 2 bytes, etc.
I bring up the datatypes because shrinking the size of the .ibd will speed up the query -- at least when the data is bigger than what can be cached.
Something unrelated:
> primary key (ctime, cid, ctype),
> index index_1 (ctime, cid)
What query is uses "index_1"? Notice how the INDEX matches the first part of the PRIMARY KEY? With one obscure exception, that INDEX is redundant. DROPping it would save a bunch of disk space and speed up inserts. (Neither of these points is important at the moment.)
The real solution may be to use "summary tables"; we can discuss that further. You can get a head start by reading
http://mysql.rjweb.org/doc.php/summarytables
But first, I want to get the datatypes and table definition fixed.