Help in optimizing a slow query...

[DDJ]

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!

[Bill Karwin]

DDJ wrote:

> 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

The first thing I notice is that you're using LEFT JOIN (which is
usually more expensive than INNER JOIN), yet you're WHERE conditions
require that columns in p be non-NULL. So the "outer" case of the LEFT
JOIN (where no row in p matches the given row in u) is eliminated from
the query. You might as well use an INNER JOIN and that'll help speed
up the query.

You also have no columns from p in the select-list, so now I'm not sure
why you're joining with that table at all.

Also, somehow the optimizer isn't using the index you have defined over
(`photo_user_id`,`photo_approvestatus`,`photo_time `).

> GROUP BY p.photo_user_id
> ORDER BY u.user_registrationdate
> LIMIT 0, 20

When you have different columns in the GROUP BY vs. the ORDER BY clause,
this results in a temporary table and filesort.

> (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)

Why group by the unique key of u, when you have no aggregate functions
on any columns of p? I guess you're trying to get the same effect as
DISTINCT.

I'd try something like this to see if it gets a more favorable EXPLAIN
report and better net performance:

SELECT u.user_id, u.user_nickname, u.user_gender, u.user_province
FROM users AS u
WHERE EXISTS(
SELECT 1 FROM photos AS p
WHERE u.user_id = p.photo_user_id
AND p.photo_approvestatus = 1)
ORDER BY u.user_registrationdate
LIMIT 0, 20

I took out the user_id <> 0 because inequalities are unlikely to get the
optimizer to use an index for it. It would be better to use user_id >
0, assuming your user_id's don't go into negative numbers.

Using a subquery requires MySQL 4.1 or later. You don't state which
version you have. Though I do appreciate that you showed the DDL for
your tables, which is something folks asking questions on the newsgroup
seldom do!

Regards,
Bill K.