MySQL query cache for expensive queries?

Question

I have this query which returns the top 10 selling products, and is used on the homepage:

SELECT a.id, a.title, SUM(b.sold) as 'sold' FROM products a
LEFT JOIN stock b ON b.product_id = a.id
GROUP BY a.id ORDER BY sold DESC LIMIT 10

This information doesn't need to be current - it only needs to update maybe once a day. So I had two questions, really:

1) Considering this is evaluating the sold SUM of every single product (from a stock table where each product has multiple rows), is this a computationally expensive query - or rather would it be if this was a large database?

2) Could MySQL cache this query and only update it once per day? I am aware it has a query cache which it uses by default, but according to that this query would be invalidated every time the number of sold items changes.

Am just looking for ways to be more efficient, to be honest.

What about using the MySQL Event Scheduler to update a static table with the results of the expensive query, on a schedule? — Michael - sqlbot
– Michael - sqlbot, Commented Feb 14, 2016 at 21:23

Chad · Accepted Answer · 2016-02-14 18:11:09Z

1

What I'd do is cache it in the code that queries the database. Keep a timestamp of the last time you queried, and if the timestamp is recent enough, return the cached result instead of running the query. You could also use a caching system like memcached or Redis... and once you've got that set up, you'll probably find a hundred other places you can use it!

Or, if there's no other content on your homepage that's dynamic, you could convert your homepage to a static HTML file that you generate once a day. Simple & fast.

answered Feb 14, 2016 at 18:11

Chad

6935 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dpavkov · Accepted Answer · 2016-02-14 18:11:20Z

1

1) It is as expensive as there are rows in the stock table.

2) No, mysql doesn't have anything like that. To cache a single query, you should use application cache. It is fairly common requirement, and how you do it depends on your application and its architecture, of course.

answered Feb 14, 2016 at 18:11

dpavkov

313 bronze badges

Collectives™ on Stack Overflow

MySQL query cache for expensive queries?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related