Best way to store XML data in a MySQL database, with some specific requirements

Question

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.

Answer 1

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....

Answer 2

The best way is to not store XML in the DB, but I have history with that particular issue.

Just store it as TEXT. 500 MB is nothing for MySql, especially with TEXT datatypes, since those aren't stored in the row buffer.