Efficiently re-saving about 20kb of text to mysql

I have a contenteditable div where users can enter up to about 20kb of text. As they're typing I want to keep autosaving that div to a mysql table so that they can later retrieve that text on their phone, tablet etc. So far I've been simply autosaving every 20 seconds by taking the whole text block and doing an update to the row in my table that holds that text.

I realize that met开发者_运维技巧hod is really inefficient and won't work once my user base grows. Is there a better way to do this? E.g, somehow take a diff between the original text and what changed and save only that? Use node.js? If you've got some idea, let me know. Thanks.

Not all texts will be 20kb, so there doesn't need to be a direct problem. You could also choose to autosave less often, and you can autosave to a session too, and only write to the database less frequently.

You could calculate the differences between the stored version (which can be kept in the session too) and the typed version. The advantage is that you don't have to use the database so often, but processing will become harder, so the load on your webserver will increase too.

I'd maybe choose to do autosaves to a memory table (you can choose the storage type in MySQL), and write a separate process/cron job, that updates the physical table in bulk on regular intervals.

this reminds me of google docs, they have a pretty nice system of autosaving large text to the database. Now I don't know about the internal components of google docs, but that is something you might want to look into.

google has much more bandwidth so their method might not work for you, though it seems like they are constantly saving data to their servers.

what you could do is use javascript and save data on the user side, and only load the data into the database when the user leaves or clicks "save". This way, when they come back to the page, as long as the savefile is there, they can get back to their file.

One way:

1. Use ajax to send the text back to server every x seconds or even by multiples of x characters (e.g every 10, 20, 30 chars and so on).

2. Use server side code (eg php) to do most of the work from this request (as its fast and efficient) where you could hold the text in session.

3. On each request the server side code could compare with the session text from the last request and would then give you the possibility to do one of the following:

   a) If start of new text is same as old session text then just update the field in database with the different using say concat:

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

   and then set session text to new text.

   b) If start text is different then you know you need to do a straight up update statement to change the field value to the new text.

Another way:

1. You only do a full update when necessary if not just a concat.
2. You can "hold" a certain amount of requests in the session before you actaully do the update eg run the ajax every 5 sec's or chars but you server side script only runs the update statement on every 5th call or 20 chars or addition 20 chars if the first part hasnt changed.