Re: Join takes way too long. Query optimizing, or denormalizing?
In my experience, you rarely want to fully normalize your data (unless it's something simple). City and country codes I wouldn't split up because they never change. Well, alright, there have been quite a few new "countries" popup in Europe recently. But you don't need to change hundreds or thousands of records, in this case, to update your data, so full normalization doesn't buy you anything. I like to think of the joke where the guy programmed 911 (US emergency phone number) on his speed dial, so to dial 911 he just had to press *78. > I guess this is not an issue on the index. Explain select says it is using > the index. So I assume, that I did just write down the join syntax wrong. > MySQL is joining all 32000 records with the city table and then looks for > the country code. So this looks ok to me. Maybe I should denormalize my data > and include the country code in the other table as well? > > Andy > - Original Message - > From: "Gelu Gogancea" <[EMAIL PROTECTED]> > To: "andy" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Monday, May 20, 2002 1:00 PM > Subject: Re: Join takes way too long. How to optimize this smal query? > >> Hi, >> Read "5.4.3 How MySQL Uses Indexes" from documentation. >> >> Regards, >> Gelu >> _ >> G.NET SOFTWARE COMPANY >> >> Permanent e-mail address : [EMAIL PROTECTED] >> [EMAIL PROTECTED] >> - Original Message - >> From: "andy" <[EMAIL PROTECTED]> >> To: <[EMAIL PROTECTED]> >> Sent: Monday, May 20, 2002 1:48 PM >> Subject: Join takes way too long. How to optimize this smal query? >> >> >>> Hi there, >>> >>> after reading a nice articel on normalisation, I did split my tables and >> now >>> I am suffering performance lost. >>> >>> this query (finding out the amount of user which have posted an article > on >>> germany) takes 4.5 s: >>> >>> SELECT count(DISTINCT user_recommending) >>> FROM recommendations t >>> INNER JOIN geo.cities AS c ON t.city_id = c.ID AND c.country_code = 'gm' >>> >>> Is there a way to optimize it? The recommendationstable has a city and >> city >>> table has the corresponding country_code. >>> So the city table has 2.5 million entries and the recommendations table >>> 32733.. >>> >>> Explain results in: >>> >>> | table | type | possible_keys| key | >>> key_len | ref | rows | Extra | >>> >> > +---++--+--+ >>> -+---+---+-+ >>> | t | index | idx_search_engine_cityid | idx_search_for_same_spot | >>> 6 | NULL | 32733 | Using index | >>> | c | eq_ref | PRIMARY,idx_cp | PRIMARY | >>> 3 | t.city_id | 1 | where used | >>> >>> Has anybody a good trick to solve boost it to a 0. time? >>> >>> Thanx, Andy >>> >>> >>> - >>> Before posting, please check: >>>http://www.mysql.com/manual.php (the manual) >>>http://lists.mysql.com/ (the list archive) >>> >>> To request this thread, e-mail <[EMAIL PROTECTED]> >>> To unsubscribe, e-mail > <[EMAIL PROTECTED]> >>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >>> >>> >> >> >> - >> Before posting, please check: >>http://www.mysql.com/manual.php (the manual) >>http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail > <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Join takes way too long. Query optimizing, or denormalizing?
* roger: Try selecting FROM the cities table and JOIN with the recommendations table. * andy SELECT count(DISTINCT user_recommending) FROM geo.cities c INNER JOIN recommendations AS t ON t.city_id = c.ID AND c.country_code = 'gm' same result. So do u think I would be better off if I save the country_code in the recommendations table as well? I did read in a sql book not to include the same data twice, but if performance is that bad?! Would you recommend to denormalize it? Andy - Original Message - From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "andy" <[EMAIL PROTECTED]> Sent: Monday, May 20, 2002 4:01 PM Subject: RE: Join takes way too long. Query optimizing, or denormalizing? > * andy > > > > > SELECT count(DISTINCT user_recommending) > > > > FROM recommendations t > > > > INNER JOIN geo.cities AS c ON t.city_id = c.ID AND > > > > c.country_code = 'gm' > > > I guess this is not an issue on the index. Explain select says it is using > > the index. So I assume, that I did just write down the join syntax wrong. > > MySQL is joining all 32000 records with the city table and then looks for > > the country code. So this looks ok to me. Maybe I should > > denormalize my data > > and include the country code in the other table as well? > > Try selecting FROM the cities table and JOIN with the recommendations table. > > -- > Roger > sql > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Join takes way too long. Query optimizing, or denormalizing?
* andy > > > SELECT count(DISTINCT user_recommending) > > > FROM recommendations t > > > INNER JOIN geo.cities AS c ON t.city_id = c.ID AND > > > c.country_code = 'gm' > I guess this is not an issue on the index. Explain select says it is using > the index. So I assume, that I did just write down the join syntax wrong. > MySQL is joining all 32000 records with the city table and then looks for > the country code. So this looks ok to me. Maybe I should > denormalize my data > and include the country code in the other table as well? Try selecting FROM the cities table and JOIN with the recommendations table. -- Roger sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Join takes way too long. Query optimizing, or denormalizing?
I guess this is not an issue on the index. Explain select says it is using the index. So I assume, that I did just write down the join syntax wrong. MySQL is joining all 32000 records with the city table and then looks for the country code. So this looks ok to me. Maybe I should denormalize my data and include the country code in the other table as well? Andy - Original Message - From: "Gelu Gogancea" <[EMAIL PROTECTED]> To: "andy" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, May 20, 2002 1:00 PM Subject: Re: Join takes way too long. How to optimize this smal query? > Hi, > Read "5.4.3 How MySQL Uses Indexes" from documentation. > > Regards, > Gelu > _ > G.NET SOFTWARE COMPANY > > Permanent e-mail address : [EMAIL PROTECTED] > [EMAIL PROTECTED] > - Original Message - > From: "andy" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Monday, May 20, 2002 1:48 PM > Subject: Join takes way too long. How to optimize this smal query? > > > > Hi there, > > > > after reading a nice articel on normalisation, I did split my tables and > now > > I am suffering performance lost. > > > > this query (finding out the amount of user which have posted an article on > > germany) takes 4.5 s: > > > > SELECT count(DISTINCT user_recommending) > > FROM recommendations t > > INNER JOIN geo.cities AS c ON t.city_id = c.ID AND c.country_code = 'gm' > > > > Is there a way to optimize it? The recommendationstable has a city and > city > > table has the corresponding country_code. > > So the city table has 2.5 million entries and the recommendations table > > 32733.. > > > > Explain results in: > > > > | table | type | possible_keys| key | > > key_len | ref | rows | Extra | > > > +---++--+--+ > > -+---+---+-+ > > | t | index | idx_search_engine_cityid | idx_search_for_same_spot | > > 6 | NULL | 32733 | Using index | > > | c | eq_ref | PRIMARY,idx_cp | PRIMARY | > > 3 | t.city_id | 1 | where used | > > > > Has anybody a good trick to solve boost it to a 0. time? > > > > Thanx, Andy > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
