CODEWITHSUNDEEP
phpmysql
daydreamer
daydreamer

Reputation: 153

Complex PHP Query

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.

  1. To find the most viewed books for the last week
  2. To find the most viewed books for the last month

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?

Upvotes: 1

Views: 95

Answers (2)

user1191247
user1191247

Reputation: 12998

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);

Upvotes: 0

Rafe
Rafe

Reputation: 3066

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

Upvotes: 2

Related Questions