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

    Unanswered: Optimizing this join query

    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:

    Code:
    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
    and the explain that goes with it:
    Code:
    +----+-------------+-------+--------+-------------------------------------------------------------------------------------------------+--------------------------------+---------+--------+---------------------------------------------------------------------+
    | 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         |                                |         |        |						                                                          |
    +----+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    Any help would be greatly appreciated.

    Clem C

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You really haven't left the database engine any option but to scan. I've "pretty printed" your query, and that might help you to understand the problem that the database engine has to solve:
    Code:
    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
    If you are using MyISAM or can choose to use it, then you could use the MySQL Full Text Search.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2004
    Posts
    16
    Thanks for "prettying" that output - should have done that myself. Unfortunately, MyISAM is not an option. But an update on this: 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 time 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.

Posting Permissions

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