Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

How can I speed up this query?

elliawelliaw EntrantCurrent User Role Beginner
Hi,

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.

Current Query:
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?

Thanks

Ee Loon

Comments

  • jamesjames Contributor Inactive User Role Beginner
    You might start off by creating a categories table and replacing the 'category' char field in 'product' with a 'categoryId' integer field.

    Do you have any indexes on your tables?

    What is the pdt_offer table?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.