I have an app, that shows data based on user preference. I will simplify it in this theoretical example:
The app shows articles filtered based on user preference for their favorite authors or their favorite topics. The data will be updated with high frequency since there will be new articles published any minute.
The approach I am going for now: The app makes a request to an endpoint with user favorites in the url params like this:
My query to db is something like this:
SELECT * FROM articles_table WHERE author_id IN (1,5,3,7,10) OR topic_id IN(3,6,9) AND (published_datetime BETWEEN '$date_today 00:00:00' AND '$date_next 00:00:00')
And I am creating an index for columns: published_datetime & author_id & topic_id.
What I want to know if there is any caching mechanism or improvements I can do to this approach to achieve best performance and best utilization of db resources?
My database server run on AWS RDS t3.medium that has 2 cpus and 4GB of ram. And I am expecting a high number of requests that reach thousands per minute, so I want to be sure I am solid about my approach and what I need to improve before I publish it to production.
I am currently caching http requests for a short ttl, with varnish. But this won't help much in this case since most requests will be unique in their combination of preference.
- WordPress - as CMS & REST API
- Varnish - as caching layer
- Mariadb - as database
- InnoDB - as table engine
Whats the best approach for my case to allow users to query db based on preference with optimal caching?
Edit: I tested my query to see how fast they run and got:
- 0.0117s on user first request with preference (unique).
- 0.0040s when another request is made to a new date after the first request (preference unchanged, date changed).
is this considered good?