On Tue, April 15, 2008 02:03, [EMAIL PROTECTED] wrote: > I'm still trying to decript the EXPLAIN result, and there is something I > really > don't understand: > > mysql> EXPLAIN SELECT STRAIGHT_JOIN media.* FROM country,content,media > WHERE > country.id='Germany' AND country.detail_tid=content.tid AND > content.id=media.content_id; > > On the FAST server: > > +--+-------+-----+----------+----------+----+----------+----+ > |id|table |type |possible_k|key |k_le|ref |rows| > +--+-------+-----+----------+----------+----+----------+----+ > |1 |country|const|PRIMARY |PRIMARY |383 |const | 1| > |1 |content|ALL |PRIMARY |NULL |NULL|NULL | 137| > |1 |media |ref |media_FI_2|media_FI_2|5 |content.id| 248| > +--+-------+-----+----------+----------+----+----------+----+ > > On the SLOW server: > +--+-------+-----+----------+----------+----+----------+------+ > |id|table |type |possible_k|key |k_le|ref | rows | > +--+-------+-----+----------+----------+----+----------+------+ > |1 |country|ref |PRIMARY |PRIMARY |383 |const | 1| > |1 |content|ALL |PRIMARY |NULL |NULL|NULL | 137| > |1 |media |ALL |media_FI_2|NULL |NULL|NULL |125649| > +--+-------+-----+----------+----------+----+----------+------+ > > On the 3rd row, on the fast server, type=ref, and ref points toward the > content.id column. > > And on the slow server, type=ALL, and ref points to NULL. Since my MySql > schema > defines explicitly the foreign key, should'n I have 'content.id' instead > of > 'NULL' ? > > What is the difference between 'possible_key' and 'key' columns ? why > key=NULL > on my slow server ? > > Tristan > > -- > Tristan Marly > http://www.linkedin.com/in/tristanmarly > Possible key refers to the keys that the server thinks might be usable. key refers to the one is chose. On the slow server it decided that the possible key would not work so it didn't use it. You might try to force the use of the key and see what happens. Did you try organizing the slow server's table. Its possibly that there were enough records added/removed that the statistics need to be updated. Just a guess.
------ William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
