Trick to Avoid Temp Table with Like?

Databases
1

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
2

the temporary table might not be caused by your use of LIKE but rather your use of GROUP BY

by the way, if you have LIKE ‘XX-YYYY-%’ then you do not also need to add an OR condition for = ‘XX-YYYY-WHITE’

:slight_smile:

3

Rudy- How do I tell what’s causing the temp table? Is there anything I can use to get mroe details about how the query is being optimized? If I remove the LIKE and match explicitly, the temp table goes away:

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'
       )
   AND products.code != 'XX-YYYY-WHITE'
   AND products.active = 1
 GROUP
    BY products.code
 LIMIT 4

Also, the two conditions are there simply because the color code is not always attached to the code and we have a few legacy codes that are like:
XX-YYYY
XX-YYYY-YELLOW
XX-YYYY-BLUE
This query handles those properly. And unfortunately, we also have style codes that can overlap (TT-85843 and TT-8584319 for example), so the hyphens are important.

4

sorry, can’t help you with the temp table any further than to suggest you keep running EXPLAINs on your queries (or portions thereof)

5

I misread your comment originally about = and LIKE. You are absolutely right. Not only that, but the LIKE doesn’t make sense there because in this query we know the code of “currentprod_prod” so we can do an explicit match. That didn’t fix the temp table problem for the entire query, but it did optimize that little section and shave almost 2 seconds off of the query! I’ll keep chugging away, thanks.