<cut>
Richard> 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
>>
Richard> 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.
Richard> Why can't both keys be used?
The problem is that the MySQL optimizer doesn't have enough information
about the distribution on key values. In this cases MySQL
unfortunately chooses the wrong key to solve the query (which makes
this query a bit slower than if it would use the optimal key).
(This is the problem with SQL; You don't say to the database 'solve
this problem this way', you just say: 'Try to solve this query
efficiently'. This is of course much harder)
Anyway; With the new MyISAM in MySQL 3.23 'myisamchk --analyze' will
update statistics for each key part. This will give MySQL much more
information about how to solve the query efficiently.
Richard> Richard Ayotte
Regards,
Monty
