Re: MySql 4.1 optimization search poor performance

[dimitar nenchev]

I am not happy because the application logic need a second item_subproduct_id
and the query is not good again, because i need extending the search by more than one sub product :(

( 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
       
    WHERE icpx.item_id = i.item_id and  
      (i.item_subproduct_id =5 OR i.item_subproduct_id2 = 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
         
    WHERE  icpx.item_id = i.item_id and 
 (i.item_subproduct_id = 4 OR i.item_subproduct_id2 = 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;

Also i found this SQL_CALC_FOUND_ROWS may be good :)



Edited 1 time(s). Last edit at 05/05/2009 10:23AM by dimitar nenchev.