1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help optimizing this crazy multi table join query

Discussion in 'MySQL Forums' started by clem_c_rock, Jan 31, 2013.

  1. clem_c_rock

    clem_c_rock Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    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 (Text):

    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?