First, I would like to thank you for such a prompt response on this problem.
Michael Widenius wrote:
> >>>>> "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
Done. Nothing.
>
> and check if this changes the EXPLAIN.
>
No.
>
> Anoter option is to do remove the key on
> closed_chk_tender_media_detail.game_date
>
Not an option, that key is heavily used in other queries.
>
> or changing the WHERE to
>
> AND closed_chk_totals.game_date = closed_chk_tender_media_detail.game_date+0
>
Interesting hack that works. And my next question will show you how much of a newbie I am
in the way a database server works.
Why can't both keys be used?
Richard Ayotte
>
> (The last option will make MySQL unable to use the key on game_date)
>
> Regards,
> Monty
