MySQL Forums
Forum List  »  Performance

Re: MySql 4.1 optimization search poor performance
Posted by: Rick James
Date: May 05, 2009 08:35AM

The STRAIGHT_JOIN is in the wrong place??
( SELECT  i.item_id,
        i.item_object_name, i.item_thumbnail_name,
        i.item_caption, i.item_headline, i.item_special_instructions,
        i.item_credit, i.item_city,i.item_primary_location,
        i.item_location, i.item_urgency, i.item_copyright_notice,
        i.item_by_line, i.item_date_created,
        i.item_view_in_cd, i.item_folder_path,
        i.item_base_dir_id, i.item_imageset_id
    FROM  tbl_item_photos i
    STRAIGHT_JOIN  tbl_items_prod_categories_xref icpx
       ON icpx.item_id = i.item_id
    AND  i.item_subproduct_id =5
      AND  (icpx.product_category_id = 24
      OR  icpx.product_category_id = 23)
    ORDER BY  i.item_date_created DESC
    LIMIT  0, 24
)
UNION DISTINCT 
( SELECT  i.item_id,
        i.item_object_name, i.item_thumbnail_name,
        i.item_caption, i.item_headline, i.item_special_instructions,
        i.item_credit, i.item_city,i.item_primary_location,
        i.item_location, i.item_urgency, i.item_copyright_notice,
        i.item_by_line, i.item_date_created,
        i.item_view_in_cd, i.item_folder_path,
        i.item_base_dir_id, i.item_imageset_id
    FROM          tbl_item_photos i
    STRAIGHT_JOIN tbl_items_prod_categories_xref icpx
         ON icpx.item_id = i.item_id
    WHERE  i.item_subproduct_id = 4
      AND  icpx.product_category_id = 11
    ORDER BY  i.item_date_created DESC
    LIMIT  0, 24
)
ORDER BY  item_date_created DESC
LIMIT  0, 24
* STRAIGHT_JOIN belongs in the FROM
* One of the joins was backwards
* WHERE -> ON
* DISTINCT -- I moved it to the UNION; you may need to move it back
* Note the extra ORDER BY and LIMIT for the UNION
* Now what is the output of EXPLAIN?
* Assuming you are doing pagination, when you want page 4 via LIMIT 72,24, the inner ones need to be LIMIT 72, with the outer one: LIMIT 72,24

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.