CODEWITHSUNDEEP
phpmysqljsoncachingbigdata
Dhiva Banyu Wigara
Dhiva Banyu Wigara

Reputation: 39

Caching on big data

I have a website with many transactions records, about 2M rows on MySQL I often need to erase the data because its getting slower when fetching the data

Database : MYSQL Lang : PHP 5.4 OS : Ubuntu 16.04

The first user will do some order, and then it will be saved in database, user will then be redirected to a "view" page for the transaction detail, then after few minutes (not long, about 1 ~ 5 minutes) it will be updated, after that the user might want to view the transaction again, since its only for 1 time use

So I've considered about caching it rather than just "erase all", but I never do it so I don't know if it's going to work or not

I planned to save the data for a while in MYSQL, only until the data got updated, and then after that I'm going to save it to a flat file, like JSON file, or inside PHP array, delete it from MYSQL, and serve it to user in case the user need it again in the future

So what is the benefits and disadvantages of what I'm going to do? Is there a limit on how many files can be stored in Ubuntu? Which one is better, store it using PHP array or JSON file?

Upvotes: 1

Views: 244

Answers (1)

Rick James
Rick James

Reputation: 142453

Don't store in JSON, it is hard to search.

Do design a schema that allows you to add INDEXes to the table(s) to search efficiently.

"Caching" is usually folly. MySQL already does an efficient amount of caching. "Putting a cache in front of a cache" rarely adds any benefit, and cost space, code, etc.

2M is not very big. Please provide some more specifics, so we can give you more specific help.

Upvotes: 1

Related Questions