1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Optimizing this join query

Discussion in 'MySQL' started by clem_c_rock, Apr 7, 2014.

  1. #1
    Hello,
    I have this query and I cannot figure out how to optimize this query any further. Since it’s a joined query, I can’t seem to get it to honor any of the indexes.

    Here’s the monstrosity:
    
    
    SELECT 
    *
    FROM assets_products ap 
    INNER JOIN assets a1 ON ap.asset_id=a1.id 
    INNER JOIN products p1 ON ap.product_id = p1.id 
    WHERE (p1.name LIKE '%9780203506561%' OR a1.isbn LIKE '%9780203506561%' OR a1.e_isbn LIKE '%9780203506561%' OR REPLACE(a1.isbn, '-','') LIKE '%9780203506561%' OR a1.isbn_10 LIKE '%9780203506561%' OR a1.isbn_13 LIKE '%9780203506561%' OR a1.print_isbn LIKE '%9780203506561%' OR a1.isbn_canonical LIKE '%9780203506561%' OR p1.sku LIKE '%9780203506561%' OR p1.sku_canonical LIKE '%9780203506561%' OR REPLACE(p1.sku, '-','') LIKE '%9780203506561%' OR (a1.author_name LIKE '%9780203506561%' OR a1.author_first_name LIKE '%9780203506561%' OR a1.author_last_name LIKE '%9780203506561%' OR p1.author_name LIKE '%9780203506561%')) AND 
    ((p1.type !='package') AND ( (a1.build_status NOT IN ('destroyed', 'unavailable', 'out_of_distribution', 'limited_distribution')) AND 
    (a1.cached_product_in_store=1 AND a1.block_search!=1 AND a1.type='VitalBook') )) 
    GROUP BY p1.id
    
    Code (markup):
    and the explain that goes with it:

    
    +----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+--------------------------------+---------+--------+---------------------------------------------------------------------+
    | id | select_type | table | type   | possible_keys                                                                                   | key                            | key_len | rows   | Extra                                                               |
    
    +----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+--------------------------------+---------+--------+---------------------------------------------------------------------+
    
    |  1 | SIMPLE      | a1    | ref    | PRIMARY,type                                                                                    | type                           | 93      | 153338 | Using index condition; Using where; Using temporary; Using filesort |
    
    |  1 | SIMPLE      | ap    | ref    | assets_products_asset_id_index,assets_products_product_id_index                                 | assets_products_asset_id_index | 4       |      1 | NULL                                                                |
    
    |  1 | SIMPLE      | p1    | eq_ref | PRIMARY,sku,products_public_id_index,sku_id_type,id_lock,in_store,index_products_on_created_on| PRIMARY                        | 4       |      1 | Using where                                                         |
    
    |   |        |     |  | products_parent_id_type,index_products_on_sku_canonical,index_products_on_company_id, |                          |         |        |                                                         |
    
    |   |        |      |   | index_products_on_created_on,index_products_on_updated_on, index_products_on_updated_on         |                                |         |        |                                                          |
    
    +----+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    
    Code (markup):
    Any help would be greatly appreciated.

    Clem C
     
    clem_c_rock, Apr 7, 2014 IP
  2. clem_c_rock

    clem_c_rock Member

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #2
    One strange optimization technique I tried yielded some pretty significant results.
    I added this forced index: FORCE INDEX (index_products_on_sku_canonical) and it's showing significant performance increases (over 50%!!!!). It's weird - when I do an explain, the old query scans 5 times less rows but I guess it has something to do with

    Using where; Using temporary; Using filesort in the fast query

    VS

    Using index condition; Using where; Using temporary; Using filesort

    in the slow query.
     
    clem_c_rock, Apr 10, 2014 IP