* Maximo Migliari > Im running the following query: > > SELECT > user.nickname, > user.id, > user_detail.points > FROM > user, > user_detail > WHERE > user.details = user_detail.id > AND user.id > 101 > AND user.language = 'en' > ORDER BY user_detail.points DESC > LIMIT 5; [...] > This is the result of the EXPLAIN for the query: > > +-------------+-------+-----------------------------+---------+--- > ------+----------------+-------+-----------------------------------------+ > | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +-------------+-------+-----------------------------+---------+--- > ------+----------------+-------+-----------------------------------------+ > | user_detail | range | PRIMARY,id,points | points | 4 | > NULL | 15567 | where used; Using index; Using filesort | > | user | ref | PRIMARY,id,details,language | details | 4 | > user_detail.id | 1 | where used | > +-------------+-------+-----------------------------+---------+--- > ------+----------------+-------+-----------------------------------------+ > > Someone told me that Using filesort is not a good sign... how can > I improve > the performace of this query? Any other tuning suggestions?
The EXPLAIN shows that the 'user_detail' table is read first, using the 'points' index. Approximately 15567 rows must be examined, and then the 'details' index of the 'user' table is used to do a lookup for each 'user_detail'. Try using STRAIGHT_JOIN: SELECT user.nickname, user.id, user_detail.points FROM user STRAIGHT_JOIN user_detail WHERE user.details = user_detail.id AND user.id > 101 AND user.language = 'en' ORDER BY user_detail.points DESC LIMIT 5; This will force mysql to read the user table first, probably using the 'language' index or the 'id' index, and then do a lookup on the 'user_detail' table. Because both tables have the approximate same number of records (32.000) in this case, and the criteria "id > 101 AND language = 'en'" probably matches most of the users, it may not help much. But it is worth a try. If most of the rows in the 'user_detail' table are relavant to this query (i.e. few users with id <= 101 and/or language<>'en') you may want to try a different approach: select the 10 (or so) highest points from 'user_details' first, then join with the 'user' table in a separate query: CREATE TEMPORARY TABLE tmp1 SELECT user_detail.id, user_detail.points FROM user_detail ORDER BY points DESC LIMIT 10; SELECT user.nickname, user.id, tmp1.points FROM tmp1 STRAIGHT_JOIN user WHERE user.details = tmp1.id AND user.id > 101 AND user.language = 'en' ORDER BY points DESC LIMIT 5; HTH, -- Roger --------------------------------------------------------------------- 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
