I am receiving XML data from a service. The test data I am receiving back has about 300 XML nodes, clearly far too many to create individual rows for in a MySQL database.
The problem is that we ideally need to store all the data, and we will probably need to reference the data again at some point in the future - we can't just process through it once and delete the XML string.
What's the best way of storing this data in a MySQL database?
I have forecast that at the predicted rate within a few months, if we were to store the raw XML data in TEXT format, the database could grow to around 500MB. In the long run this feels impractical.
You could create a blob column (i.e. mediumtext column). Instead of inserting XML purely as strings in the DB, you could zip the XML, then store in MySQL.
When you read from MySQL, you unzip it again. Since XML is text you'll get very high compression rates (close to 80% compression). The thought process being, disk IO takes a lot longer time than compression/un-compression which is predominantly Processor bound.
The downside being you will no longer be able to query or do full text search using SQL....