MySQL Forums
Forum List  »  Performance

in (id_1,id_2, ...) or more select statements or
Posted by: Steven Chan
Date: December 08, 2004 11:34PM

Hi,

I am designing a web application. Table A is a list of customers and table B is a list of items each customer has purchased. pri_key of A is customer_id and there is an index on customer_id in B.

I need to return cutomers names as well as addresses and descriptions (large text field) from Table A as well as return the count of purchases from Table B.

At any 1 time, a query will only return about 20 customers because I limit it.

Which is the best performance wise:

a) Join A to B and group by customer_id, name, address, description
b) select customers from A. Loop through them to build a string of customer_ids like id_1,id_2,id_3... and then select from B dropping the string into an IN() in the WHERE clause.
c) select from A. Then:
select from B where customer_id = id_1
select from B where customer_id = id_2
select from B where customer_id = id_3
...
...
etc.

Thanks in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
in (id_1,id_2, ...) or more select statements or
3605
December 08, 2004 11:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.