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
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.