This is the explain select sql query on this table containing 500 records now:
| table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+----------------------+--------------+---------+---------- -+------+---------------------------------+ | l | ALL | idx_userid | NULL | NULL | NULL | 2193 | Using temporary; Using filesort | | u | eq_ref | PRIMARY,country_code | PRIMARY | 3 | l.user_id | 1 | | | c | eq_ref | country_code | country_code | 2 | u.country | 1 | | | ct | eq_ref | PRIMARY | PRIMARY | 3 | u.city | 1 | | does that help? Andy ----- Original Message ----- From: "Jay Blanchard" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, May 16, 2002 3:25 PM Subject: RE: Speeding up a huge sql query > [snip] > SELECT DISTINCT > u.user_id, > u.user_name, > max( > if(u.sex=2,25,0) + > if(u.age=4,25,0) + > if(u.country='gm',25,0)+ > if(l.language_id='de',25,0) > ) > AS ranking, > > c.country_code, > c.country, > ct.city, > l.language_id, > u.age, > u.sex > >FROM userdb.user u, userdb.user_languages l, test.countries AS c > > LEFT JOIN test.cities AS ct ON ct.ID = u.city > > WHERE > l.user_id = u.user_id > AND u.country = c.country_code > > GROUP BY u.user_id > HAVING ranking > 0 > ORDER BY ranking desc > Limit 0,10 > [/snip] > What are your table indexes? Can you do an EXPLAIN on the query? > > Thanks! > > Jay > > > --------------------------------------------------------------------- > 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
