List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 29 1999 12:03pm
Subject:slow JOIN when extra conditions in WHERE clause
View as plain text  
>>>>> "rayotte" == rayotte  <rayotte@stripped> writes:

>> Description:
rayotte> Here is the speedy query...

rayotte> SELECT empl
rayotte> , tender_media
rayotte> , SUM(tender_media_total) AS tender_media_total
rayotte> , SUM(tender_media_count) AS tender_media_count
rayotte> FROM closed_chk_totals, closed_chk_tender_media_detail
rayotte> WHERE closed_chk_totals.number = closed_chk_tender_media_detail.check
rayotte> AND closed_chk_totals.rvc = closed_chk_tender_media_detail.revenue_center
rayotte> AND closed_chk_totals.chk_opn_time = closed_chk_tender_media_detail.open_time
rayotte> GROUP BY empl, tender_media;

rayotte> Here is the EXPLAIN:
rayotte> table				type	possible_keys				key		key_len	ref				rows	Extra
rayotte> closed_chk_totals		ALL	k_revenue_center,k_number		NULL		NULL	NULL				9055
rayotte>
> closed_chk_tender_media_detail	ref	k_check,k_open_time,k_revenue_center	k_open_time    
> 3	closed_chk_totals.chk_opn_time	13	where used

rayotte> real    0m1.068s

rayotte> And then I add

--> AND closed_chk_totals.game_date = closed_chk_tender_media_detail.game_date

rayotte> to the WHERE clause and the query now looks like:

rayotte> SELECT empl
rayotte> , tender_media
rayotte> , SUM(tender_media_total) AS tender_media_total
rayotte> , SUM(tender_media_count) AS tender_media_count
rayotte> FROM closed_chk_totals, closed_chk_tender_media_detail
rayotte> WHERE closed_chk_totals.number = closed_chk_tender_media_detail.check
rayotte> AND closed_chk_totals.rvc = closed_chk_tender_media_detail.revenue_center
rayotte> AND closed_chk_totals.chk_opn_time = closed_chk_tender_media_detail.open_time
rayotte> AND closed_chk_totals.game_date = closed_chk_tender_media_detail.game_date
rayotte> GROUP BY empl, tender_media;

rayotte> Here is the EXPLAIN:
rayotte> table				type    possible_keys						key		key_len	ref				rows    Extra
rayotte> closed_chk_totals		ALL    
> k_revenue_center,k_number,k_game_date			NULL		NULL	NULL				9055
rayotte> closed_chk_tender_media_detail	ref    
> k_check,k_open_time,k_game_date,k_revenue_center	k_game_date	4	closed_chk_totals.game_date	10	where
> used

rayotte> But the execution time is now 11m4.575s!!! Yikes.

rayotte> I tried using the STRAIGHT_JOIN option without any good results.


>> Fix:
rayotte> I don't know. Is it in the JOIN optimizer routine?

Yes. The problem is that MySQL seams to use the wrong key in the
second case.  Try using: 

isamchk -a table_name
mysqladmin flush-tables

and check if this changes the EXPLAIN.

Anoter option is to do remove the key on
closed_chk_tender_media_detail.game_date

or changing the WHERE to

AND closed_chk_totals.game_date = closed_chk_tender_media_detail.game_date+0

(The last option will make MySQL unable to use the key on game_date)

Regards,
Monty
Thread
slow JOIN when extra conditions in WHERE clauserayotte27 Sep
  • slow JOIN when extra conditions in WHERE clauseMichael Widenius29 Sep
  • Re: slow JOIN when extra conditions in WHERE clauseRichard Ayotte4 Oct
    • Re: slow JOIN when extra conditions in WHERE clauseMichael Widenius6 Oct