SELECT p.id_product
FROM popularity p, products pr
WHERE p.id_product = pr.id_product
AND p.date >= (DATE_SUB(CURDATE(), INTERVAL 30 DAY))
GROUP BY p.id_product order by count(p.id_product) desc LIMIT 0, 6
I'm wondering how to optimize my queries as much as possible. I'm trying to trim off the fat and this query in particular is giving me trouble because it is used everywhere. I have a table in which I store each instance of a product page being visited, and another table which has the products.
p.id_product is a non-unique key, whereas pr.id_product is the products' table primary key. I don't think I'm doing my query right as it seems like I'm doing a full index scan to do my join between both tables. And I'm not sure calling DATE_SUB in such a way is the best idea (I'm looking at the past month), or if my group/order is correct. My database's tables also run off InnoDB.
Is there any way I can make this faster? I've already set a limit to the query since I noticed the software never uses more than 6 results
Use show create table popularity and show create table products to determine your indexes already in place.
Indexes speed up performance but slow updates and inserts.
You need to find the right balance of indexes based on different ways of going after your data, so I am not one to say just pepper the tables with indexes.