Help optimizing this crazy query

[c73mr0ck]

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:

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?