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
