Current CTO exploring entrepreneurship on the side; coach; mentor; instructor.
Dedicated to promoting digital literacy and ideological diversity in tech.
It sounds a lot like you're trying to shoehorn non-relational data into a relational system.
You lose all of the performance support of a relational system when your primary data collection is json. It's difficult to index and more complex for MySQL to store.
This is more of an issue of using a hammer like you would a screwdriver: you're simply not using the tool correctly.
Either maintain your data as JSON documents and move to a document-storage NoSQL solution like mongo (easier), or refactor your data management into a relational model to work with MySQL (harder).
Oops, I clearly made a mistake in describing the issue! I clarified a bit above. When we were trying to use MySQL for this, the data was not JSON. You do bring up a good point though in that we don't need the power of a relational database (e.g. joins) for this sort of data. I have been looking at DynamoDB to see if that will give us what we need. Though maybe Mongo would have better tooling.
Current CTO exploring entrepreneurship on the side; coach; mentor; instructor.
Dedicated to promoting digital literacy and ideological diversity in tech.
Reading over some of your other comments implies that it's only a subset of your data that has this problem, not the entire dataset.
In that case, mixing storage engines is probably the preferred solution. If this "transient data" is short-lived between periods of long persistence, then redis makes a lot of sense.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
It sounds a lot like you're trying to shoehorn non-relational data into a relational system.
You lose all of the performance support of a relational system when your primary data collection is json. It's difficult to index and more complex for MySQL to store.
This is more of an issue of using a hammer like you would a screwdriver: you're simply not using the tool correctly.
Either maintain your data as JSON documents and move to a document-storage NoSQL solution like mongo (easier), or refactor your data management into a relational model to work with MySQL (harder).
Oops, I clearly made a mistake in describing the issue! I clarified a bit above. When we were trying to use MySQL for this, the data was not JSON. You do bring up a good point though in that we don't need the power of a relational database (e.g. joins) for this sort of data. I have been looking at DynamoDB to see if that will give us what we need. Though maybe Mongo would have better tooling.
Reading over some of your other comments implies that it's only a subset of your data that has this problem, not the entire dataset.
In that case, mixing storage engines is probably the preferred solution. If this "transient data" is short-lived between periods of long persistence, then redis makes a lot of sense.