MySQL Forums
Forum List  »  Performance

Re: MySql 4.1 optimization search poor performance
Posted by: dimitar nenchev
Date: May 05, 2009 09:13AM

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.

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.