Happy new year everyone. I'm hoping for some general guidance in the following situation...
I have an application that has been running for about 10 years. The datastore is in mysql, (now on AWS Aurora).
Some of the tables that are in one-to-many relations are starting to have a larger number of rows:
Records (~1.4million rows) | V (1 to many) | V SubRecords (~10million rows) | V (1 to many) | V SubSubRecords (~22million rows)
There is not a lot of actual data being stored in these rows (ie subSubRecords is about 5gb in total), and the queries I run are very straightforwards, using indexed keys with no joins. For example...
SELECT ... FROM Records WHERE id = ?; SELECT ... FROM SubRecords WHERE recordId = ?; SELECT ... FROM SubSubRecords WHERE subRecordId = ?;
So far, everything continues to be highly performant.
However, I'm starting to worry about how this design will hold up over time. While it took 10 years to get to 22 million rows in SubSubRecords, the db is growing a lot faster now. I wouldn't be surprised to see that table climb to 100million rows over the next 5 years, which feels like a lot. And I'm not sure at what point it will become a problem.
I realize this is a rather broad question, and is situation-dependent. But what what types of solutions are generally recommended in these cases?
Setup partitions? (The tables use foreign keys to enforce integrity and my understanding is that this is incompatible with partitions.)
Convert the data in subRecords and subSubRecords to a json payload and store that directly in a json column in the main records table? (Same amount of data, but less rows, if it matters.)
Move to an entirely different db? (Mongo? I know nothing about it but have heard is better at scaling in certain situations.)
Ignore it until it becomes an issue? :D
Any suggestions / pearls of wisdom from those who have wrestled with similar problems is welcome. Thanks (in advance) for your help!
As requested, here is the CREATE TABLE syntax for the above tables...
CREATE TABLE records ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, typeId TINYINT(1) UNSIGNED NOT NULL, userId INT UNSIGNED NOT NULL, updated TIMESTAMP DEFAULT NOW() NOT NULL, savename VARCHAR(100) NOT NULL, title VARCHAR(100) NOT NULL, instructions TEXT NOT NULL, FULLTEXT ftRecords(savename, title), PRIMARY KEY(id), FOREIGN KEY(typeId) REFERENCES recordTypes(id), FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB CHARACTER SET=utf8; CREATE TABLE subRecords ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, recordId INT UNSIGNED NOT NULL, thumbnailId INT UNSIGNED NULL, sortOrder SMALLINT NOT NULL, enabled TINYINT(1) DEFAULT 0 NOT NULL, title VARCHAR(100) NOT NULL, instructions TEXT NOT NULL, parameters VARCHAR(500) NOT NULL, PRIMARY KEY(id), FOREIGN KEY(recordId) REFERENCES records(id) ON DELETE CASCADE, FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARACTER SET=utf8; CREATE TABLE subSubRecords ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, subRecordId INT UNSIGNED NOT NULL, thumbnailId INT UNSIGNED NULL, sortOrder SMALLINT NOT NULL, caption VARCHAR(200) NOT NULL, PRIMARY KEY(id), FOREIGN KEY(subRecordId) REFERENCES subRecords(id) ON DELETE CASCADE, FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL ) ENGINE=InnoDB CHARACTER SET=utf8;