need help optimizing MySQL query

Question

I have a complex SQL query with nested 'subqueries' that I want to optimize

    SELECT
'likes' `type`,
entry_date item_date,
CASE `type`
    WHEN 'entry_id' THEN entry_id
    WHEN 'member_id' THEN f.member_id
    WHEN 'topic_id' THEN entry_id
END    
AS item_id,
CASE `type`
    WHEN 'entry_id' THEN (SELECT title FROM exp_weblog_titles WHERE entry_id=item_id)
    WHEN 'member_id' THEN (SELECT screen_name FROM exp_members WHERE member_id=item_id)
    WHEN 'topic_id' THEN (SELECT title FROM exp_forum_topics WHERE topic_id=item_id)
END    
AS item_title,
CASE `type`
    WHEN 'member_id' THEN (SELECT username FROM exp_members WHERE member_id=item_id)
    ELSE ''
END    
AS  item_url_title,
f.type parent_type,
CASE `type`
    WHEN 'entry_id' THEN (SELECT weblog_id FROM exp_weblog_titles WHERE entry_id=item_id)
    WHEN 'member_id' THEN ''
    WHEN 'topic_id' THEN (SELECT forum_id FROM exp_forum_topics WHERE topic_id=item_id)
END    
AS parent_id,
CASE `type`
    WHEN 'entry_id' THEN ''
    WHEN 'member_id' THEN ''
    WHEN 'topic_id' THEN (SELECT forum_name FROM exp_forums WHERE forum_id=parent_id)
END    
AS parent_title,         
m.member_id actor_member_id,
screen_name actor_screen_name,
username actor_username,
photo_filename actor_photo,
avatar_filename actor_avatar
FROM exp_favorites f, exp_members m
WHERE f.member_id=m.member_id

As you can notice, in subqueries some table are queried several times for the same row but a different field.
Like, I have

SELECT screen_name FROM exp_members WHERE member_id=item_id

and

SELECT username FROM exp_members WHERE member_id=item_id

which I'd live to see combined to mimize the number of reuests and server load.

Is there a way to do this? Any advices?

UPD. table structure is following:
CREATE TABLE exp_favorites (
favorites_id int(10) unsigned NOT NULL auto_increment,
type varchar(16) NOT NULL default 'entry_id',
author_id int(10) unsigned NOT NULL default '0',
entry_id int(10) unsigned NOT NULL,
member_id int(10) unsigned NOT NULL,
site_id smallint(3) unsigned NOT NULL default '1',
entry_date int(10) unsigned NOT NULL,
notes text NOT NULL,
public char(1) NOT NULL default 'y',
PRIMARY KEY (favorites_id),
KEY author_id (author_id),
KEY entry_id (entry_id),
KEY member_id (member_id),
KEY site_id (site_id),
KEY public (public),
KEY type (type)
)

Can you post your table structure on here? Is your database normalized? — Abe Miessler
– Abe Miessler, Commented Sep 23, 2010 at 18:03
updated the post with table structure info. I don't know much about normalization, but it looks normalized to me. — Yuri Salimovskiy
– Yuri Salimovskiy, Commented Sep 23, 2010 at 18:55

D'Arcy Rittich · Accepted Answer · 2010-09-23 18:05:27Z

1

You can do something like this:

SELECT
'likes' `type`,
entry_date item_date,
CASE `type`
    WHEN 'entry_id' THEN entry_id
    WHEN 'member_id' THEN f.member_id
    WHEN 'topic_id' THEN entry_id
END    
AS item_id,
CASE `type`
    WHEN 'entry_id' THEN wt.title
    WHEN 'member_id' THEN m2.screen_name
    WHEN 'topic_id' THEN ft.title
END    
AS item_title,
...
FROM exp_favorites f
inner join exp_members m ON f.member_id=m.member_id
left outer join exp_weblog_titles wt on wt.entry_id=f.item_id
left outer join exp_members m2 on m2.entry_id=f.item_id
left outer join exp_forum_topics ft on ft.entry_id=f.item_id

answered Sep 23, 2010 at 18:05

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Yuri Salimovskiy Over a year ago

it does not look as option as f.item_id can relate to record in any of 3 tables, and thus the join will become just huge. Or am I wrong here?

user359040 Over a year ago

@runner: look at the join conditions in the last three table joins - all of them are from f.item_id, to the appropriate field in each table.

user359040 Over a year ago

@RedFilter, I think a couple of those left joins need to be tweaked - exp_members is only ever joined to on member_id, so there is no need for m2, while forum topic is joined to on topic_id and parent_id but not entry_id.

D'Arcy Rittich Over a year ago

@Mark: yeah there are likely so inaccuracies; without seeing a full schema I am not going to spend any more time on this - I think the concept is clear enough.

Yuri Salimovskiy Over a year ago

the problem is that f.item_id can refer to row in any of 3 tables (I use 'type' field todetermine what data needs to be used). So I think the join here might grow enourmously. Anyway, I'll try this solution and will see whether it's better then what I use or not.

|

Collectives™ on Stack Overflow

need help optimizing MySQL query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related