I have a MySQL database with a column
Body MEDIUMTEXT. Until now I used to only store the contents into it. There was no update option for the users of the application. Now, I wanted to add an update option to the content(To make it easy, just think it like a stack exchange editing a post scenario).
I know about not optimizing something until it's needed but at the same time I don't want to design a system now and later realise it was a stupid thing to do in the first place.
So, I got some ideas:
Retrieving the whole
Body from the database and comparing both character by character.
The problem in this idea is: Database overhead(even though it is for inserts, I guess the connecting, sending, parsing and closing is same for every query) of sending a query(2 units), parsing(2 units), retrieving the huge data over the network (even though it's on the same datacenter but still there will be some latency... right?) and the load on application server to actually hold the data in the variables and comparing both the strings(both Memory and CPU load)
MEDIUMTEXT is 16MB max. So, in the worst case, I need to transfer that over network and hold that data(old and new) in a variable in the application server. So, even if I'm handing 32 requests at a time, it will occupy 1GB of RAM (32MB * 32 req = 1024MB) and additional CPU load while checking that content.
It might be in very rare cases that 16MB will be sent every time and the more inefficient thing here is actually comparing that 16MB content character by character. But is it too much to always estimate the worst case while designing a system?
Storing a hashed value of
Body in a column and comparing the hashes.
I store an additional column in the table with
hashedBody value of the
Body. Whenever an update comes to the server, I just retrieve that
hashedBody value from the database. Here I can save on network transfer by transferring only
hashBody value. Now, I hash the new content came from the User and compare the smaller hashes and update if the hashes are different.
But if the hashing algorithm is fast, chances are they might encounter a collision and the new content which is different will not be updated and if the hashing algorithm is secure and slow, there are less chances for collision but I might use more CPU than in the first naive method above.
And for the questions I have:
Is it too much to always estimate the worst case while designing a system?
Is the first method good?
Is the second method good? Are there any hash algorithms other than md5? Like less collisions and fast?
Is there any other new approach or idea to tackle this?
P.S. Yeah I know that I'm clearly overthinking about this but I'm afraid not considering these small mistakes/design can cause problems which I can't foresee. I just want to make a decision based on all the information and know all the problems that I can or will encounter in the future rather than make a decision based on ignorance and come across problems out of the blue and panic.
I forgot to mention but I'm maintaining a
History table to update that with old values(like maintaining a versioning system). So, I can't just update. I need old values to insert it into
History table and new values into