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?
What about fid ? Are they inserted in random or sequential orfer ?
If order is random clustering by (date,fid) may be faster and you can add separate key on fid.
Regarding what happens if page becomes full with new insert - in this case page split happens and so you get two pages instead of one. One of them will be stored in the same place while other moved to different location - this is how fragmentation occures.
The rows are inserted in random fid order. We considered clustering on (date,fid), and adding a separate index for fid. However, since almost all lookups are done by fid, we wanted to optimize for reads to occur on the clustered index.
Overall this table will be much more read heavy than insert heavy (thankfully we will never need to update this table), so maybe this is the best we can get. Do you have any tips for dealing with innodb table fragmentation?