One of the seniors in my team raised that we should store database records in memory using code. His reason was that this would be more "efficient" than using database when we retrieve the record. I want to find out is this a good approach or not?
We are using MariaDB, running on a raspberry using docker, request rate hardly > 1 per/sec
A record will be unique by N fields (prop1, prop2,…prop n). And we have to retrieve a record using queries like
SELECT d.id FROM.. WHERE d.prop1 =…, d.prop2=…,…
The table which we run this query will likely to never exceed 1000 rows
My colleague said that: we will store data in the database but at the same time, create an object in memory so that we can retrieve the record "faster"
I, personally, do not agree with this.
Since I believe the search algorithm, as I do my research, using in database (B-tree) is very efficient and ensure a balanced tree structure. If we end up implementing that it's like re-inventing the wheel. Otherwise I could not imagine how are we going to achieve the same speed as the database
Moreover, we will have to update the object, which is unnecessary complicated and time consuming. Not to mention that the object could become large, since we do not have a mechanism to manage its size, and cause memory corruption. It's like caching, but if we are mentioning cach, why not use Redis in the first place?
Please let me know your opinions, I would be pleased to hear about pros and cons of this