How can I speed up multi select query?

[elliaw]

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

[kvdd]

"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

[tesuji]

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

[elliaw]

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.

:)

[jakesee]

Actually I think the lowest part would the LIKE clause... isn't it?

[petsol]

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...