3

I want to store text differences in MySQL. Let's say I have a text, 500 characters or so, and then someone edits the text. I want to be able to reconstruct both the old and new text.

From a database viewpoint, I thought it would be stupid to store both texts if I could just store the difference. That would save a lot of space, especially if the edits are minute and the texts huge. But how do I do that?

I had some ideas which were really complex, such as storing only the old text, and when an edit occurs, replace the changed parts with a certain variable which then refers to another table. That other table could simply store the new and old value of that particular section of the text.

Is there a simple way to do this?

3

If you store old text and differences, you will have to always apply everything to get current text. So I suggest storing current text and diff (sort of reversed changes) to get previous version. Keep table for those diffs like (text_id, version_number, diff).

For differences you can sure use standard diff tool. And if you want to save space, use some compression (either on mysql side or application side).

Do you need to search in those texts? If yes then in current version only or in all versions? The latter might be a bit bigger challenge to do effectively.

3
  • Thanks, yes you're right. store newer version is way better. I don't need to be able to search through earlier text. How would such a diff tool be implemented with mysql. I haven't really worked with anything like that. Any hint?
    – koljanep
    Jun 19 '15 at 19:30
  • @koljanep then no compression (or "low level" table compression) and fulltext index on text is a good choice probably (only in newer versions for innodb), and the separate table for diffs and those you can compress freely, unless average diff is so small it has detrimental effect - that depends on compression method used, but say if you expect changes under 100 characters, then probably do not bother or only prepare for it in the future if it will start making more sense.
    – jkavalik
    Jun 19 '15 at 19:34
  • 1
    Oh I missed the question about diff - that is not a good function for mysql, that should be your application job - php has extension, javascript package, C++
    – jkavalik
    Jun 19 '15 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.