Hello,

I'm trying since weeks (with no luck...) to optimize a query which is
"killing" my site on peak times (100+ visitors simultaneously). I almost
gave up.... I really hope someone more expert than me here can give me some
help... Here's the "slow" query:

SELECT u.user_id, u.user_nickname, u.user_gender, u.user_province
FROM users AS u
LEFT JOIN photos AS p ON u.user_id = p.photo_user_id
WHERE u.user_id <> 0
AND u.user_id = p.photo_user_id
AND p.photo_approvestatus = 1
GROUP BY p.photo_user_id
ORDER BY u.user_registrationdate
LIMIT 0, 20

(I used the "GROUP BY" option because I need to group the result by the
"p.photo_user_id" column where I have duplicated USER ID)





If I try to use the EXPLAIN command here's what I get as result:

---------------------------------------------------------------------------
SIMPLE p range photo_user_id_approvestatus_time,
photo_approvestatus_time photo_approvestatus_time 1 NULL
8032 Using where; Using temporary; Using filesort

SIMPLE u eq_ref PRIMARY PRIMARY 3
mydatabase.p.photo_user_id 1 Using where
---------------------------------------------------------------------------





My table's structure looks like this:

---------------------------------------------------------------------------
CREATE TABLE `users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`user_name` varchar(20) NOT NULL default '',
`user_gender` tinyint(1) unsigned NOT NULL default '0',
`user_province` varchar(50) NOT NULL default '0',
`user_registrationdate` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`user_id`),
KEY `user_registrationdate` (`user_registrationdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12726 ;


CREATE TABLE `photos` (
`photo_id` mediumint(8) unsigned NOT NULL auto_increment,
`photo_user_id` mediumint(8) unsigned NOT NULL default '0',
`photo_description` varchar(35) NOT NULL default '',
`photo_time` int(11) unsigned NOT NULL default '0',
`photo_approvestatus` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`photo_id`),
KEY `photo_user_id_approvestatus_time`
(`photo_user_id`,`photo_approvestatus`,`photo_time `),
KEY `photo_approvestatus_time` (`photo_approvestatus`,`photo_time`),
KEY `photo_time` (`photo_time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23676 ;
---------------------------------------------------------------------------




Does anyone have any idea on how to optimize this query and make it faster?
How to get ride of the evil "Using temporary; Using filesort" ?

Thank you for any help!