The problem is here:
RANGE (floor(starttime))
Partition pruning is not smart enough to understand the characteristics of arbitrary functions. This one should be easy, since it is monotonic.
Bite the bullet. Change to one of the few datatypes that PARTITION understands. When partitioning on hour, I have used MEDIUMINT (only 3 bytes), and pre-calculated it via
hr = UNIX_TIMESTAMP(ts)/3600
By "precompute", I mean that I added the `hr` field and filled it in as I was inserting in the table. Then I did
PARTITION BY RANGE(hr)
from2012111100 VALUES LESS THAN (375729),
from2012111101 VALUES LESS THAN (375730),
...
future VALUES LESS THAN MAXVALUE
Note that I named the partitions in a useful way:
from2012111101 has the 1am values, and it ends at 2am:
SELECT FLOOR(UNIX_TIMESTAMP('2012-11-11 02:00:00')/3600) = 375730
A side note: There is almost no reason to have the secondary key here:
add PRIMARY KEY (`bts_gid`,`starttime`),
add KEY `bts_gid` (`bts_gid`),
since the PRIMARY KEY handles any lookups by bts_gid. Recommend removing the key.
decimal(14,0) takes 7 bytes.
decimal(20,0) takes 9 bytes.
Since you have a table big enough to consider partitioning, the table is big. So you should work hard at shrinking the fields as much as practical.
You have not said why you think PARTITIONing might be beneficial.
* Will you be purging "old" data? DROP PARTITION is excellent for this.
* Will all your SELECTs include a WHERE clause on the datetime? (Of course, with my suggestion, the conversion needs to be done in the WHERE clause. Suggest creating a Stored Function.)
More notes:
http://mysql.rjweb.org/doc.php/partitionmaint