Efficiently re-save about 20 kilobytes of text in mysql

Question

I have a contenteditable div where users can enter up to 20 kilobytes of text. When they are printed, I want to save the autosave of this div in the mysql table, so that later I can get this text on my phone, tablet, etc. Until now, I just autosave every 20 seconds, taking the entire text block and updating the row in my table containing that text.

I understand that the method is really inefficient and will not work as soon as my user base grows. Is there a better way to do this? For example, somehow take the difference between the source text and what has changed and save just that? Use node.js? If you have an idea, let me know. Thank you

Answer 1

Not all texts will be 20kb, so there should be no direct problem. You can also choose autosave less often, and you can also autosave to a session and write less to the database.

You can calculate the difference between a saved version (which can also be stored in a session) and a typed version. The advantage is that you don’t need to use the database often, but the processing will be more complicated, so the upload to your web server will also increase.

Perhaps I want to do autosave in the memory table (you can choose the type of storage in MySQL) and write a separate process task / cron, which regularly updates the physical table in bulk.

Answer 2

this reminds me of google docs, they have a pretty good system for automatically saving large text to a database. Now I don’t know about the internal components of Google docs, but this is what you might want to learn.

Google has a lot more bandwidth, so their method may not work for you, although it seems that they constantly store data on their servers.

what you can do is use javascript and save the data on the user side and load the data only into the database when the user leaves or clicks “save”. That way, when they return to the page while the save file is being saved, they can return to their file.

Answer 3

One of the methods:

• Use ajax to send text to the server every x seconds or even multiple x characters (e.g. every 10, 20, 30 characters, etc.).

• Use server-side code (e.g. php) to do most of the work from this request (both quickly and efficiently), where you could hold text in a session.

• For each request, the server-side code can be compared with the session text from the last request and then gives you the opportunity to do one of the following:

  a) If the start of the new text is the same as the old text of the session, simply update the field in the database with another using concat:

  update table set field=concat(field,'extra data...') where id='xx' 

  and then set the session text to the new text.

  b) If the text of the beginning is different from the other, then you know that you need to follow the instructions to update up to change the value of the field to new text.

Another way:

• If necessary, you perform a full upgrade, if not just concat.
• You can “hold” a certain number of requests in a session before you activate the update, for example, run ajax every 5 seconds or characters, but on the server side the script only executes the update instruction for every 5th call or 20 characters or add 20 characters if the first part has not changed.