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_created 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_exhibitions']." e,"
.$CONFIG['tbl_albums']." a,"
.$CONFIG['tbl_members']." m,"
.$CONFIG['tbl_organizations']." o,"
.$CONFIG['tbl_s_symbols']." ss
LEFT JOIN
".$CONFIG['tbl_exhibitions_rating_general']." erg
ON erg.exhibition_id = e.exhibition_id
LEFT JOIN
".$CONFIG['tbl_exhibitions_favorites']." ef
ON ef.exhibition_id = 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_id
AND e.album_id = a.album_id
AND e.datetime_created > 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,reference_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_created datetime NOT NULL default '0000-00-00 00:00:00',
datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (journal_id),
KEY group_id1 (group_id1,group_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_created` datetime NOT NULL default '0000-00-00 00:00:00',
`datetime_modified` 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`,`datetime_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