0

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

5
Contributors
5
Replies
6
Views
9 Years
Discussion Span
Last Post by petsol
1

"SELECT *" e.g. select everything is always a bad idea.

To speed up, select only the things you want/need.

WOW! I look again at your query and cannot believe my eyes :D because you select everything (*) from two tables!

Things you need to do:
1) Select what you want: e.g. "SELECT x.id,y.pid FROM blabla..."
2) Use indexes to speed up you queries, for more info about indexes look here:
- http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
- http://www.tutorialized.com/tutorial/Optimizing-MySQL-Queries-and-Indexes/7139
- and you'd better look here: http://www.tizag.com/mysqlTutorial/mysql-index.php

Votes + Comments
Right.
0

Hello elliaw,

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

Actually, this query is wrong! It should not work! Because of * (the sign of slackers) more than one column will be selected. Therefore, GROUP BY clause must contain more than as x.pid. If you don't get an error message, something is wrong with your data base system.

>>> 3. I need to get the image, min(price) and title for each new product.

It is a very good idea to follow kvdd's advice !

So select these three columns only and put them into GROUP BY clause. But attention: I really doubt heavily that that mysql would be able to manage an image (what type, a blob type?) in that place!

krs,
tesu

0

Thanks guys,

I did read up the articles as suggested and I am able to simplify the query and select only fields that is required.

:)

0

tesui, in mySQL you can do this: not bringing everything in the group by section, although it is not standard SQL. My experience is that it is very hard to make a group by fast, although building good indexes help a lot. The worst thing I experienced is that group by time exponentially grows with records. So only do a goup by with an index...

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.