Growing database pains
100M rows is not scary.
Partitioning -- No.  It is unlikely to add any performance benefit.  If, however, you need to purge &quot;old&quot; data, there might be a use for partitioning.
If recordId is an INDEX of SubRecords -- Good.
If recordId is the first column in the PRIMARY KEY of SubRecords -- better.
Show us SHOW CREATE TABLE for further advice.
Your 3 SELECTs would run faster if you JOINed the 3 tables in a single SELECT.
Clustering
A slight improvement would be to improve the &quot;clustering&quot; of the rows that you fetch at the same time.  For SubRecords,
ALTER TABLE SubRecords
    DROP PRIMARY KEY(id),
    ADD PRIMARY KEY(recordId, id),
    ADD INDEX(id);

That way, when you get the several SubRecords for one Record, they will be located next to each other.  This is because the PRIMARY KEY (in InnoDB) is 'clustered' with the data.  The INDEX(id) is to keep AUTO_INCREMENT happy.
This change may not show any noticeable improvement until the dataset is bigger than RAM.
A similar thing can be done with SubSubRecords.