Quote:
|
I am creating a searching api for an e-shop with a mysql db.
|
Is this going to be an API used by many apps, or is it a purpose-built system?
Quote:
|
I need to retreive the common product_id's from the selected by the user Manufacturer,Price....etc
|
Your first option looks very much like you're trying to do the join in your app. Even if you could write a better join algorithm than the MySQL team, you'll be making a huge number of requests to the DBMS and incurring a lot of latency.
Code:
SELECT Products.product_id FROM Products WHERE Products.product_id IN (SELECT product_id FROM Manufacturers_xref_prods WHERE manuf_id=5) AND Products.product_id IN(SELECT product_id FROM Prices_xref_prods WHERE price_id=6) ;
Those subqueries are entirely equivalent to:
Code:
SELECT Products.product_id
FROM Products p INNER JOIN Manudacturers m
ON p.product_id = m.product_id
INNER JOIN Prices_xref_prods x
ON p.product_id = x.product_id
WHERE m.manuf_id = 5 AND x.price_id = 6
Now, which will perform better? It depends on the optimizer. A good optimizer can recognize a subquery and convert it to the equivalent join. It's unlikely that the join will perform worse than the subquery, unless the joins aren't ordered well and confuse the optimizer.
Generally, try to make your queries readable and minimize the amount of logic in your application. Unless rewriting a query gives you an order of magnitude increase in performance, you may find some future upgrade will make all your careful optimizations obsolete.
dBforums
>
Lot of small queries or a Large 1
11-27-09, 11:39
Linear Mode
