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