Performance for query filtered by user preference

Question

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:

• https://example.com/api/articles/today?=fav_autohrs=1,5,3,7,10&topics=3,6,9

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.

My setup:

• 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?

Answer 1

My query to db is something like this:
author_id IN ('1,5,3,7,10') OR topic_id IN('3,6,9')

Hopefully only something like that.
Double check you "IN" clause syntax - I doubt you have an topic_id of '3,6,9'.

As to performance ...

"WHERE .. IN" can present problems of its own, particularly with long lists of values but there's probably not much you can do about that one.

Another operator that might perform surprisingly badly is "OR". Finding records based on this condition or that one (or some other one) forces the database to "second-guess" itself and choose less efficient scanning plans. Breaking the query down into simpler, "all-inclusive" conditions and then "bolting" the results back together is what the database prefers.

You might try this as an alternative:

SELECT *
  FROM articles_table
  WHERE author_id IN ( 1, 5, 3, 7, 10 ) 
  AND published_datetime BETWEEN '$date_today 00:00:00' AND '$date_next 00:00:00'
UNION 
SELECT *
  FROM articles_table
  WHERE topic_id IN ( 3, 6, 9 ) 
  AND published_datetime BETWEEN '$date_today 00:00:00' AND '$date_next 00:00:00'

Or, to reduce the load on the duplicate-removing "UNION":

SELECT * 
from articles_table 
where article_id in 
( SELECT article_id
    FROM articles_table
    WHERE author_id IN ( 1, 5, 3, 7, 10 ) 
    AND published_datetime BETWEEN '$date_today 00:00:00' AND '$date_next 00:00:00'
  UNION 
  SELECT article_id
    FROM articles_table
    WHERE topic_id IN ( 3, 6, 9 ) 
    AND published_datetime BETWEEN '$date_today 00:00:00' AND '$date_next 00:00:00'
  ) 

Answer 2

"Caching" usually involves exactly the same query being run twice. Probably different users are looking for slightly different things, hence a caching layer is likely to be useless.

MariaDB (and MySQL) use a cache called the "buffer_pool". This is at a lower level, hence still useful when different users are fetching different info. It is tuned by innodb_buffer_pool_size which may have a terribly low setting of 128M. Change it to 1500M.

As already discussed, IN with a quote string is simply wrong. IN a list of numbers is inefficient, OR is 'impossible' to optimize, though UNION helps. Also, the date range is also a drag on performance.

The only way the Optimizer will run your query is by checking every row in the table. In some situations, one of these may help; add them all:

INDEX(author_id)
INDEX(topic_id)
INDEX(published_datetime)

The column names don't look like WordPress, is that really the app?

Will you also have ORDER BY and/or LIMIT?

11.7ms sounds great for a SELECT like yours.

One new new article per minute is not a problem; even one per second is not a problem.

But the bigger the table gets, the slower the query will be.