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
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 | | | | |
+----+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Any help would be greatly appreciated.
Clem C