Query Optimization

[ensnare]

This query is running REAL slow ... like 1.2 secs ... any ideas on how
I could optimize it? Perhaps my indexes are incorrect?

$this->query = "SELECT m.username as username,
e.title as title,
e.exhibition_id as
exhibition_id,
LEFT(e.text,50) as
text,
e.random_key,
e.server_id,
e.datetime_crea ted as
datetime,
e.views as views,
a.title as
album_title,
a.album_id as
album_id,
o.subdomain as
subdomain,
ss.symbol as symbol,
COUNT(DISTINCT
erg.exhibitions _rating_general _id) AS num_ratings,
AVG(erg.value) AS
rating,
COUNT(DISTINCT
ef.exhibitions_ favorite_id) AS num_favorites,
COUNT(DISTINCT
c.comment_id) AS num_comments
FROM ".$CONFIG['tbl_exhibition s']." e,"
.$CONFIG['tbl_albums']." a,"
.$CONFIG['tbl_members']." m,"
.$CONFIG['tbl_organizati ons']." o,"
.$CONFIG['tbl_s_symbols']." ss
LEFT JOIN
".$CONFIG['tbl_exhibition s_rating_genera l']." erg
ON erg.exhibition_ id = e.exhibition_id
LEFT JOIN
".$CONFIG['tbl_exhibition s_favorites']." ef
ON ef.exhibition_i d = e.exhibition_id
LEFT JOIN ".$CONFIG['tbl_comments']." c
ON ( c.reference_id = e.exhibition_id
AND
c.type_id = 4 )
WHERE m.organization_ id = $organization_i d
AND e.album_id = a.album_id
AND e.datetime_crea ted > now() -
interval 50 day
AND m.organization_ id =
o.organization_ id
AND m.symbol_id = ss.symbol_id
AND e.member_id = m.member_id
AND (e.scope_id = 5 OR e.scope_id = 3)
AND e.active = $active
AND m.active = $active
GROUP BY erg.exhibition_ id
ORDER BY $order $sort";
+-------+--------+-----------------------------------+---------------+---------+-----------------+-------+---------------------------------+
| table | type | possible_keys | key |
key_len | ref | rows | Extra |
+-------+--------+-----------------------------------+---------------+---------+-----------------+-------+---------------------------------+
| o | const | PRIMARY | PRIMARY |
2 | const | 1 | Using temporary; Using filesort |
| e | ALL | member_id | NULL |
NULL | NULL | 12034 | Using where |
| a | eq_ref | PRIMARY | PRIMARY |
2 | e.album_id | 1 | |
| m | eq_ref | PRIMARY,symbol_ id,organization _id | PRIMARY |
3 | e.member_id | 1 | Using where |
| ss | eq_ref | PRIMARY,symbol_ id | PRIMARY |
2 | m.symbol_id | 1 | |
| erg | ref | exhibition_id | exhibition_id |
4 | e.exhibition_id | 1 | |
| ef | ref | exhibition_id | exhibition_id |
4 | e.exhibition_id | 1 | |
| c | ref | type_id,referen ce_id | reference_id |
4 | e.exhibition_id | 10 | |
+-------+--------+-----------------------------------+---------------+---------+-----------------+-------+---------------------------------+

CREATE TABLE journals (
journal_id smallint(10) NOT NULL auto_increment,
member_id smallint(7) NOT NULL default '0',
title varchar(100) NOT NULL default '',
text text NOT NULL,
reference_link varchar(100) NOT NULL default '',
location varchar(100) NOT NULL default '',
scope_id tinyint(4) NOT NULL default '5',
group_id1 int(7) NOT NULL default '0',
group_id2 mediumint(7) NOT NULL default '0',
group_id3 int(7) NOT NULL default '0',
datetime_create d datetime NOT NULL default '0000-00-00 00:00:00',
datetime_modifi ed datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (journal_id),
KEY group_id1 (group_id1,grou p_id2,group_id3 ),
KEY member_id (member_id),
FULLTEXT KEY text (text)
) TYPE=MyISAM;

CREATE TABLE `comments` (
`comment_id` int(10) NOT NULL auto_increment,
`member_id` int(10) NOT NULL default '0',
`reference_id` int(10) NOT NULL default '0',
`title` varchar(25) NOT NULL default '',
`text` text NOT NULL,
`datetime_creat ed` datetime NOT NULL default '0000-00-00 00:00:00',
`datetime_modif ied` datetime NOT NULL default '0000-00-00 00:00:00',
`root_id` int(10) default NULL,
`type_id` char(1) NOT NULL default '',
`path` text NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `member_id` (`member_id`),
KEY `root_id` (`root_id`),
KEY `type_id` (`type_id`,`dat etime_created`) ,
KEY `reference_id` (`reference_id` ,`type_id`)
) TYPE=MyISAM AUTO_INCREMENT= 162949 ;

[Bill Karwin]

ensnare wrote:

This query is running REAL slow ... like 1.2 secs ... any ideas on how
I could optimize it? Perhaps my indexes are incorrect?

It looks like you're trying to be too clever, using outer joins to find
a way to compute several aggregates at the same time as getting
non-aggregate data results. Don't do that!

Execute a separate query for each of your aggregate computations. That
is, pull the COUNT() and AVG() aggregates out of that monster query, and
do them each as a separate query.

Your SQL will become much easier to understand, much easier to maintain,
and I'm betting it will run much faster as separate queries than as one
hopelessly overcomplicated query.

Regards,
Bill K.

[steve]

"Bill Karwin" wrote:

ensnare wrote:

This query is running REAL slow ... like 1.2 secs ... any ideas

on how

I could optimize it? Perhaps my indexes are incorrect?
It looks like you’re trying to be too clever, using outer joins
to find
a way to compute several aggregates at the same time as getting
non-aggregate data results. Don’t do that!

Execute a separate query for each of your aggregate computations.
That
is, pull the COUNT() and AVG() aggregates out of that monster

query, and
do them each as a separate query.

Your SQL will become much easier to understand, much easier to
maintain,
and I’m betting it will run much faster as separate queries than
as one
hopelessly overcomplicated query.

Regards,
Bill K.

I second Bill. This is the most complex query I have seen in my life.
Break it apart and time the components. You would have a much easier
time optimizing each component (building indecis, etc.). That is
easily done if you are running the query from a script (e.g. php), and
you will negligible performance penalty from the breakup.

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Query-...ict139345.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=466919