Yes i know about the same limit every where. I a developing other things and i am testing this issue fast and on the fly :|
Any way this query is very good. I change the syntax because it did not work your way, i did not know about union distinct. I try also select distinct .. on the 2 selects and it was filesorting and using tmp againg, so the union distinct is the better.
( 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
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
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;
Explain:
1 PRIMARY i index PRIMARY item_date_created 3 \N 2597611 Using where
1 PRIMARY icpx ref FK_tbl_items_prod_categories_xref,item_id item_id 4 photoshot_v2.i.item_id 1 Using where
2 UNION i index PRIMARY item_date_created 3 \N 2597611 Using where
2 UNION icpx ref FK_tbl_items_prod_categories_xref,item_id item_id 4 photoshot_v2.i.item_id 1 Using where
\N UNION RESULT <union1,2> ALL \N \N \N \N \N Using filesort
How do you advice me to change the query when there are fulltext search?
( 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 (MATCH(i.item_caption, i.item_keywords) AGAINST ('london' IN BOOLEAN MODE ))
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
WHERE icpx.item_id = i.item_id
AND (MATCH(i.item_caption, i.item_keywords) AGAINST ('london' IN BOOLEAN MODE ))
AND 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;
EXPLAIN:
1 PRIMARY i fulltext PRIMARY,simple_search simple_search 0 1 Using where; Using filesort
1 PRIMARY icpx ref FK_tbl_items_prod_categories_xref,item_id item_id 4 photoshot_v2.i.item_id 1 Using where
2 UNION i fulltext PRIMARY,simple_search simple_search 0 1 Using where; Using filesort
2 UNION icpx ref FK_tbl_items_prod_categories_xref,item_id item_id 4 photoshot_v2.i.item_id 1 Using where
\N UNION RESULT <union1,2> ALL \N \N \N \N \N Using filesort
It's better, it not use tmp :) but it is not using order by index :(
Anyway i fond that there is another bad query that i fixed already, i select
things from 1 table joined with thb_items and then filter it on in(which is index) form the second table which causes tmp file creating, lucky the same id(again index) has and the 1st table, after chaning to it it starts shine :)
Edited 1 time(s). Last edit at 05/05/2009 09:14AM by dimitar nenchev.