SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trick to Avoid Temp Table with Like?

    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:
    Code MySQL:
    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:
    Code MySQL:
    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:
    Code MySQL:
    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
    MySQL v5.1.58
    PHP v5.3.6

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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'

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code mysql:
    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.
    MySQL v5.1.58
    PHP v5.3.6

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, can't help you with the temp table any further than to suggest you keep running EXPLAINs on your queries (or portions thereof)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    MySQL v5.1.58
    PHP v5.3.6


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •