Re: Two MySql servers, but very different performances for a SELECT JOIN
Selon Rob Wultsch <[EMAIL PROTECTED]>: > http://www.google.com/search?q=STRAIGHT_FORWARD+mysql yields buckus. > Where is this documented? oops, I meant 'straight_join' :/ Tristan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
On Wed, Apr 16, 2008 at 12:41 AM, <[EMAIL PROTECTED]> wrote: > The 'STRAIGHT_FORWARD' + 'FORCE INDEX' worked, thanks a lot to all :) http://www.google.com/search?q=STRAIGHT_FORWARD+mysql yields buckus. Where is this documented? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
The 'STRAIGHT_FORWARD' + 'FORCE INDEX' worked, thanks a lot to all :) Tristan -- Tristan Marly 06.16.84.57.43 http://www.linkedin.com/in/tristanmarly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
On Tue, Apr 15, 2008 at 9:05 AM, Wm Mussatto <[EMAIL PROTECTED]> wrote: > 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. I suggested an ANALYZE TABLE, and he already showed that this did not fix his problem. Is there a more in depth method to update statistics? The FORCE INDEX might be a good suggestion, however I think there is something very wonky with that table. Have you tried DROPing and reloading it from a dump? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
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]
Re: Two MySql servers, but very different performances for a SELECT JOIN
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
Tristan Marly schrieb: First, thanks for all your suggestions and for beeing so reactive. @Martin: the explain result was in attachment, but you will have more results in this current mail. @Rob: you are right, the 'show index' shows strange things, cf. below. @Rodolphe: indeed the STRAIGHT_JOIN has been very helpfull, cf. below. @Brent: thanks for this very precise and technical answer. too bad, that all the other list members could not read what was so helpful ... reply to the author instead of to the list is very useful for the audience ... or was it only me who missed their replies (except from Rub Wulsch)? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySql servers, but very different performances for a SELECT JOIN
(cf. in attachment my notes. I attached there to avoid unwanted carriage returns) -- First, thanks for all your suggestions and for beeing so reactive. @Martin: the explain result was in attachment, but you will have more results in this current mail. @Rob: you are right, the 'show index' shows strange things, cf. below. @Rodolphe: indeed the STRAIGHT_JOIN has been very helpfull, cf. below. @Brent: thanks for this very precise and technical answer. So now, here is where I am: == 1st comparison: using JOIN clause, starting with the 'media' table == mysql> EXPLAIN SELECT media.* FROM media JOIN content ON content.id=media.content_id JOIN country ON country.detail_tid=content.tid AND country.id='Germany'; On the FAST server: ++-+-+---+---++-+--+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-+---+---++-+--+--+-+ | 1 | SIMPLE | country | const | PRIMARY | PRIMARY| 383 | const|1 | | | 1 | SIMPLE | content | ALL | PRIMARY | NULL | NULL| NULL | 137 | Using where | | 1 | SIMPLE | media | ref | media_FI_2| media_FI_2 | 5 | integration.content.id | 248 | Using where | ++-+-+---+---++-+--+--+-+ On the SLOW server: ++-+-++---+-+-+++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-++---+-+-+++-+ | 1 | SIMPLE | country | ref| PRIMARY | PRIMARY | 383 | const | 1 | Using where | | 1 | SIMPLE | media | ALL| media_FI_2| NULL| NULL| NULL | 167531 | | | 1 | SIMPLE | content | eq_ref | PRIMARY | PRIMARY | 4 | integration.media.content_id | 1 | Using where | ++-+-++---+-+-+++-+ => The optimizer still joints in different order (country/content/media on the fast, country/media/content on the slow) == 2nd comparison: using JOIN clause, starting with the 'country' table == (as interestingly suggested by Brent) mysql> EXPLAIN SELECT media.* FROM country JOIN content ON country.detail_tid=content.tid JOIN media ON content.id=media.content_id WHERE country.id='Germany'; => No changements, exactly the same results as the 1st comparison. == 3rd comparison: using STRAIGHT_JOIN == 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 | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-+---+---++-+--+--+-+ | 1 | SIMPLE | country | const | PRIMARY | PRIMARY| 383 | const|1 | | | 1 | SIMPLE | content | ALL | PRIMARY | NULL | NULL| NULL | 137 | Using where | | 1 | SIMPLE | media | ref | media_FI_2| media_FI_2 | 5 | integration.content.id | 248 | Using where | ++-+-+---+---++-+--+--+-+ On the SLOW server: ++-+-+--+---+-+-+---++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+-+-+---++-
Re: Two MySql servers, but very different performances for a SELECT JOIN
2008/4/11 <[EMAIL PROTECTED]>: > Hi, > > I have 2 MySql server instances. One which is 5.0.27/Debian, another > 5.0.32/Solaris. > > Both instances have the same data in the database. > > And I'm doing a select: > SELECT media.* FROM media,country,content WHERE country.id='Germany' AND > country.detail_tid=content.tid AND content.id=media.content_id; > > This request takes less than a half second on one server, and takes 70 > seconds > on another server. > > The EXPLAIN results are attached to this mail. Its shows that there are > interpreted very differently on each server. > > I checked the database structure, wich is exactly identical on both instance. > > Moreover, one could think that this is the optimizer which does not interpret > the joint request in the right order in the version 5.0.32 compared to > 5.0.27, > but I'm pretty sure that this application had worked in the past (good > performance on 5.0.27). > > Do you know how could I found some clues ? Shot in the dark, run ANALYZE TABLE on all the relevant tables on the slow server. If you just imported them the indexes may not be well distributed. Please post ddl and relevant SHOW INDEX info. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two MySql servers, but very different performances for a SELECT JOIN
Hi, I have 2 MySql server instances. One which is 5.0.27/Debian, another 5.0.32/Solaris. Both instances have the same data in the database. And I'm doing a select: SELECT media.* FROM media,country,content WHERE country.id='Germany' AND country.detail_tid=content.tid AND content.id=media.content_id; This request takes less than a half second on one server, and takes 70 seconds on another server. The EXPLAIN results are attached to this mail. Its shows that there are interpreted very differently on each server. I checked the database structure, wich is exactly identical on both instance. Moreover, one could think that this is the optimizer which does not interpret the joint request in the right order in the version 5.0.32 compared to 5.0.27, but I'm pretty sure that this application had worked in the past (good performance on 5.0.27). Do you know how could I found some clues ? Tristan -- Tristan Marly 06.16.84.57.43 http://www.linkedin.com/in/tristanmarly mysql> explain select media.* from media,country,content where country.id='Germany' and country.detail_tid=content.tid and content.id=media.content_id; on the 'fast' server: ++-+-+---+---++-+--+--+-+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra | ++-+-+---+---++-+--+--+-+ | 1 | SIMPLE | country | const | PRIMARY | PRIMARY| 383 | const|1 | | | 1 | SIMPLE | content | ALL | PRIMARY | NULL | NULL| NULL | 140 | Using where | | 1 | SIMPLE | media | ref | media_FI_2| media_FI_2 | 5 | integration.content.id | 279 | Using where | ++-+-+---+---++-+--+--+-+ on the 'slow' server: ++-+-++---+-+-+++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-++---+-+-+++-+ | 1 | SIMPLE | country | ref| PRIMARY | PRIMARY | 383 | const | 1 | Using where | | 1 | SIMPLE | media | ALL| media_FI_2| NULL| NULL| NULL | 180443 | | | 1 | SIMPLE | content | eq_ref | PRIMARY | PRIMARY | 4 | integration.media.content_id | 1 | Using where | ++-+-++---+-+-+++-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
