Trick to Avoid Temp Table with Like?

[BrandonK]

It seems like using the LIKE comparison in MySQL is causing my query to use a temporary table, despite (what I believe to be) proper indices. Here's the issue:

I have a query that takes a product and finds 4 other products in the same category as it and which are not the same style. Here's the query:

SELECT currentprod_cat.cat_id
 
     , products.id AS product_id
     , products.code AS product_code
     , products.name AS product_name
     , products.price AS price
     , products.reg_price AS reg_price
     , products.thumbnail AS product_thumbnail
     , products.auto_photos AS auto_photos
     , products.active AS active
  FROM s01_Products currentprod_prod
INNER
  JOIN s01_MUS_CategoriesXProducts currentprod_catxprod
    ON currentprod_catxprod.prod_id = currentprod_prod.id
INNER
  JOIN s01_MUS_Categories currentprod_cat
    ON currentprod_cat.cat_id = currentprod_catxprod.cat_id
   AND currentprod_cat.active = 1
INNER
  JOIN s01_MUS_CategoriesXProducts mus_catxprod
    ON mus_catxprod.cat_id = currentprod_cat.cat_id
INNER
  JOIN s01_Products products
    ON products.id = mus_catxprod.prod_id
 WHERE (
           currentprod_prod.code = 'XX-YYYY-WHITE'
        OR currentprod_prod.code LIKE 'XX-YYYY-%'
       )
   AND products.code != 'XX-YYYY-WHITE'
   AND products.code NOT LIKE 'XX-YYYY-%'
   AND products.active = 1
 GROUP
    BY products.code
 LIMIT 4

Don't pay too much attention to this query, however, because I can simplify it greatly and still have the same issue. What is important is how we use the "code" field. The XX part of the code is the vendor and the YYYY is the style. WHITE is the color (but not always present; code could be simply ZZ-UUUU). Anyways, here's the simplified query:

SELECT products.id AS product_id
     , products.code AS product_code
     , products.name AS product_name
     , products.price AS price
     , products.reg_price AS reg_price
     , products.thumbnail AS product_thumbnail
     , products.auto_photos AS auto_photos
     , products.active AS active
  FROM s01_Products currentprod_prod
     , s01_Products products
 WHERE (
           currentprod_prod.code = 'XX-YYYY-WHITE'
        OR currentprod_prod.code LIKE 'XX-YYYY-%'
       )
   AND products.code != 'XX-YYYY-WHITE'
   AND products.code NOT LIKE 'XX-YYYY-%'
   AND products.active = 1
 GROUP
    BY products.code
 LIMIT 4

Now this query doesn't produce the same results (it simply gets 4 other products that are not the same style), but it has the same issue. It uses a temporary table on the "products" table.

Now I can "solve" the performance issue by simply removing the two LIKE comparisions, but then the query will return products of similar style, which is not what we want. I'm curious if anyone knows a good hack or workaround for this. I know that LIKE comparisons can use indices, but I'm not sure how to prevent this temp table (which is really killing the performance of the query).

Here's how the table's indices are set up:

CREATE TABLE `s01_Products` (
   ...
 
   UNIQUE KEY `s01_Products_1` (`id`),
   KEY `date_added` (`date_added`),
   KEY `active+code` (`active`,`code`),
   KEY `code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1