I have 2 tables.
books (id, sku, name, description, date_added)
and
books_views (id, sku, date_viewed)
I am trying to write an optimized query to do the following.
The books_views table has more than 4 million entries. What would be the best way to get the data sorted by views for week and month?
The query is:
SELECT sku, count(*) AS times_viewed
FROM book_views bv
WHERE date_viewed > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY sku
ORDER BY times_viewed DESC
To get the views for the month, change the interval to 30 days.
To make it fast, you need to make sure that the table is indexed properly. You'll want an index on date_viewed for sure. If you're going to want the book names as well, you'll want to index the sku columns in both tables. Here's how you'd get the book names as well.
SELECT bv.sku, name, count(*) AS times_viewed
FROM book_views bv JOIN books b ON bv.sku = b.sku
WHERE date_viewed > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY bv.sku
ORDER BY times_viewed DESC
times_viewed? It's an alias.
Unless your books_views table has other fields that you are not showing here you should change your views table to - books_views (sku, date_viewed, views) with the PK on sku and date_viewed.
Then modify your insert to be an insert on dup key -
INSERT INTO books_views VALUES ('sku', CURRENT_DATE, 1)
ON DUPLICATE KEY UPDATE views = views + 1;
If you want the best performance, assuming more updates than inserts you could do -
UPDATE books_views
SET views = views + 1
WHERE sku = 'sku'
AND date_viewed = CURRENT_DATE;
then check for the number of affected rows, and then do insert if no rows affected -
INSERT INTO books_views VALUES ('sku', CURRENT_DATE, 1);
books_viewsfor each view.