List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 5 1999 10:12pm
Subject:Re: slow JOIN when extra conditions in WHERE clause
View as plain text  
<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
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