MySQL

How can I speed up multi select query?

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2008

Posts: 2

Reputation: elliaw is an unknown quantity at this point

Solved Threads: 0

elliaw

Offline

Newbie Poster

How can I speed up multi select query?

Jul 11th, 2008

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

Last edited by peter_budo; Jul 11th, 2008 at 7:29 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Jun 2008

Posts: 55

Reputation: kvdd is an unknown quantity at this point

Solved Threads: 2

kvdd

Offline

Junior Poster in Training

Re: How can I speed up multi select query?

Jul 11th, 2008

"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

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/...l-indexes.html
- http://www.tutorialized.com/tutorial...d-Indexes/7139
- and you'd better look here: http://www.tizag.com/mysqlTutorial/mysql-index.php

•

Join Date: Apr 2008

Posts: 296

Reputation: tesuji is on a distinguished road

Solved Threads: 42

tesuji

Offline

Posting Whiz in Training

Re: How can I speed up multi select query?

Jul 12th, 2008

Hello elliaw,

•

Originally Posted by 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

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.