Can anyone help? I have 2 table each over 2M records and it is taking ages retrieving records.
1. A 'product' table that contains all products in store.
2. The second table is the 'offer' table which contains all offers related to the products (e.g price,store,product condition).
1. 'product' table fields
[pid, country, category, image]
2. 'offer' table fields
[offer_id, pid, store_id, condition, price, title, stock]
3. I need to get the image, min(price) and title for each new product.
SELECT * FROM product x, offer y WHERE x.pid=y.pid AND x.country = 'US' AND x.category like 'auto|accessories' AND y.condition = 'new' AND y.price=( select min(z.price) from pdt_offer z where z.pid=y.pid AND z.quality='new' AND z.stock>0) AND y.stock >0 GROUP BY x.pid
Is there a better way to do this?