Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Unanswered: Lot of small queries or a Large 1

    Hi

    I am creating a searching api for an e-shop with a mysql db.

    I use these tables from the db
    1)Products
    1)Manufacturers_xref_prods
    2)Prices_xref_prods
    3)Attributes_xref_prods

    each of these has a "product_id" column.
    I need to retreive the common product_id's from the selected by the user Manufacturer,Price....etc

    I have 2 options to do that
    1) Run small queries that refers on 1 table for each of the above tables

    (SELECT product_id FROM Manufacturers_xref_prods WHERE manuf_id=5)
    and then find the common product_id's beetween the tables with php.

    2) Run a large query that returns the desired results.

    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) ;

    Does anyone knows Which option offers better query performance?

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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?

    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.
    Last edited by sco08y; 11-27-09 at 17:05.

  3. #3
    Join Date
    Nov 2009
    Posts
    21
    Thank you very much

    Yes the api will work for a lot aplications.
    1) Joins perform better even if there are no indexes in tables ?
    2) Could you suggest me an optimizer or a way to optimize my queries ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •