>>>>> "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
