Say I have an innodb table with three columns:
The primary key for this table is fid,date and if I understand correctly, InnoDB will cluster on this key, so the data will be sequential on disk. In this case, fid and val are foreign keys to other tables, and date is stamped with now() on every insert. Additionally, most queries need to lookup by fid, so clustering on date,fid would not be ideal.
Can someone explain to me how room gets made on disk when an insert occurs on this table? Specifically, I’m wondering what happens when a page for a given fid,date is full. Does MySQL make room in the next page by moving data around so it can stay sequential on disk? Or is another page allocated at the end, and is the data not really seqential anymore until some later rebuild?