Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    16

    Unanswered: Help optimizing this crazy query

    We have 4 table relationship, 2 main tables - "assets" and "products", a through table - "assets_products" and a products/price relationship. Here's the gist of the tables with only the relevant fields:

    assets
    +----------------------------------+--------------+------+-----+---------------------+-----------------------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------------------------+--------------+------+-----+---------------------+-----------------------------+
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | in_store | tinyint(4) | YES | | 0 | |
    | isbn | varchar(32) | YES | MUL | NULL | |
    +----------------------------------+--------------+------+-----+---------------------+-----------------------------+

    products;
    +------------------------+---------------+------+-----+---------------------+-----------------------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------------------+---------------+------+-----+---------------------+-----------------------------+
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | sku | varchar(40) | YES | UNI | NULL | |
    | type | varchar(30) | YES | | NULL | |
    | asset_id | int(11) | YES | | NULL | |
    | in_store | tinyint(4) | NO | MUL | 0 | |
    | parent_id | int(11) | YES | MUL | NULL | |
    +------------------------+---------------+------+-----+---------------------+-----------------------------+


    assets_products;
    +------------+----------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +------------+----------+------+-----+---------+----------------+
    | asset_id | int(11) | NO | MUL | 0 | |
    | product_id | int(11) | NO | MUL | 0 | |
    +------------+----------+------+-----+---------+----------------+


    prices;
    +----------------------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +----------------------+--------------+------+-----+---------+----------------+
    | price | decimal(8,2) | NO | | 0.00 | |
    | currency | varchar(3) | NO | MUL | USD | |
    | effective_begin_date | datetime | YES | | NULL | |
    | effective_end_date | datetime | YES | | NULL | |
    | priceable_id | int(11) | YES | MUL | NULL | |
    | priceable_type | varchar(255) | YES | MUL | NULL | |
    +----------------------+--------------+------+-----+---------+----------------+




    The first relationship is called a Single relationship and here's a summary of the relationships:
    1. ONE TO ONE MATCH of assets/products through assets_products
    2. The product type is set to 'Single' and the assets.isbn MATCHES the products.sku

    The second relationship is a called a Rental/Demo relationship:
    1. The product type is either 'Rental' or 'Demo'
    2. the products.parent_id is set to the id of a 'parent' product which is a Single relationship (ONE TO ONE MATCH of assets/products through assets_products)

    The 3rd relationship is called a Package
    1. A package is a collection of all the above (Singles, Singles + Rentals or Demos) and they can be seen as "children" of the Package
    2. A package can access it's "children" by all the asset_ids that match the Package's product_id in the assets_products table.

    Each product is linked to the prices table by the prices.pricable_id = products.id and prices.pricable_type=products.type (Package or Product) and we need to show products with valid prices which are donated by prices.effective_begin_date <= 'today' AND prices.effective_end_date >= '2020-23-01'

    The other main criteria that drives this query is Singles have cached_product_in_store=1 and products have and in_store=1.

    Now, the dilemma. I'm trying to write a search by asset.title and products.name search all in one query and the madness looks like this:

    Code:
    SELECT 
    	a1.id AS aid, p1.id AS pid, p1.type, p1.in_store as pin_store, a1.cached_product_in_store, p1.parent_id, a1.title, p1.name
    FROM assets a1
    INNER JOIN (assets_products, products p1, prices) ON
    	 p1.id = assets_products.product_id AND
    	 a1.id = assets_products.asset_id AND
    	 p1.id = prices.priceable_id AND
    	 prices.effective_begin_date <= '2013-23-01' AND
    	 prices.effective_end_date >= '2020-23-01' AND
    	 prices.priceable_type IN ('Product', 'Package') AND
    	 prices.currency = 'USD'
    WHERE 
    	(CASE
    		WHEN ((p1.type='Rental' OR p1.type='Demo') AND p1.parent_id IS NOT NULL) THEN (p1.in_store=(SELECT in_store FROM products p2 WHERE p1.parent_id=p2.id AND p2.in_store=1)) AND
    			(LOWER(p1.name) LIKE '%Global%' AND LOWER(p1.name) LIKE '%History%' AND LOWER(p1.name) LIKE '%&%' AND LOWER(p1.name) LIKE '%Geography%')
    		WHEN (p1.type='Package') THEN (p1.in_store=1) AND
    			(LOWER(p1.name) LIKE '%Global%' AND LOWER(p1.name) LIKE '%History%' AND LOWER(p1.name) LIKE '%&%' AND LOWER(p1.name) LIKE '%Geography%')
    		WHEN (p1.type='Single') THEN (a1.cached_product_in_store=1) AND
    			(LOWER(a1.title) LIKE '%Global%' AND LOWER(a1.title) LIKE '%History%' AND LOWER(a1.title) LIKE '%&%' AND LOWER(a1.title) LIKE '%Geography%')
    	END)
    GROUP BY p1.id
    It's close but the GROUP BY p1.id is doubling the execution time of the query.

    Any ideas?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, I'm not so familiar with MySQL dialects(?) like...
    Code:
    ...
     INNER JOIN (assets_products, products p1, prices) ON
    ...
    or
    Code:
    ...
    WHERE 
    	(CASE
    ...
    ...
    	END)
    /*
    Note: No comparison operator, like =, <>, so on...
    */
    GROUP BY p1.id
    this example might be worth to try
    (It might be too simplified
    , but it was too troublesome for me to describe the steps to reach this query.)
    Example 1:
    Code:
    SELECT MAX(a1.id)                      AS aid
         , p1.id                           AS pid
         , MAX(p1.type)                    AS type
         , MAX(p1.in_store)                AS pin_store
         , MAX(a1.cached_product_in_store) AS cached_product_in_store
         , MAX(p1.parent_id)               AS parent_id
         , MAX(a1.title)                   AS title
         , MAX(p1.name)                    AS name
     FROM  assets          AS a1
     INNER JOIN
           assets_products AS ap
      ON   ap.asset_id     = a1.id
     INNER JOIN
           products        AS p1
      ON   p1.id           = ap.product_id
     INNER JOIN
           prices          AS pr
      ON   pr.priceable_id = p1.id
       AND '2013-23-01' BETWEEN pr.effective_begin_date
                            AND pr.effective_end_date
       AND pr.priceable_type IN ('Product' , 'Package')
       AND pr.currency       =  'USD'
     WHERE
           LOWER(p1.name) LIKE '%global%'
       AND LOWER(p1.name) LIKE '%history%'
       AND LOWER(p1.name) LIKE '%&%'
       AND LOWER(p1.name) LIKE '%geography%'
       AND CASE
           WHEN p1.type IN ('Rental' , 'Demo')
            AND EXISTS
                (SELECT 0
                  FROM  products p2
                  WHERE p2.id       = p1.parent_id
                    AND p2.in_store = 1
                ) THEN
                p1.in_store
          /* OR 1 ? */
           WHEN p1.type = 'Package' THEN
                p1.in_store
           WHEN p1.type = 'Single'  THEN
                a1.cached_product_in_store
            END
          = 1
    GROUP BY
          p1.id
    Last edited by tonkuma; 02-02-13 at 14:11. Reason: Add " /* OR 1 ? */ "

Posting Permissions

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