PK of 2 columns - to reduce disk seek operations

[Alexey Kulavert]

Hi all!

I have a InnoDB table with following columns:
TaskID int not null auto_increment primary key,
ClusterID int not null,
other few columns...
Select from that table mostly uses ...ClusterID=XXXX_const... condition and therefore ClusterID has an index based on it. The data in ClusterID are not unique and have about 0,1% of different values and decreasing. But the data which fit mentioned search condition are spread in the space of the table storage. So many search operations produce many disk reads and now disk I/O is becoming a bottleneck.

There are different ways to grow up performance which I dont wish to discuss here, such as partitioning, moving index files to separate physical disk, migrating to other SQL database, migrating to Key-Value database and so on...

I wish to ask what you think about the following way:
reorganize PK to the one containing 2 columns: ClusterID first, TaskID second. I expect that MySQL will physically store data with equal ClusterID together in one disk page (or few neighboring pages), that will lead to significant decrease of disk seek operations.
But I have a question here about how this way would influence Insert operations? Are there any settings/parameters such as "fill factor", telling DB-engine to leave some free space for future inserts not to be expensive?