Ant Suggestions For Improving This Query?

[manishk3008]

Query:-

 

select
t . *, events.event_time as last_time
from
events,
(
(
	select
		bonding.type,
			bonding.global_id2 as target_post,
			bonding.target_id as on_whose_post,
			GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
			GROUP_CONCAT(bonding.what_global_id) as shooted_what,
			MAX(bonding.what_global_id) as last,
			'bonding' as flag
	from
		bonding
	where
		bonding.type = 1
			and bonding.shooter_id in (select
				`user2`
			from
				relation_table
			where
				`user1` = 192)
	group by bonding.global_id2
)
union
(
select
		bonding.type,
			bonding.global_id2 as target_post,
			bonding.target_id as on_whose_post,
			GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
			GROUP_CONCAT(bonding.what_global_id) as shooted_what,
			MAX(bonding.what_global_id) as last,
			'bonding' as flag
	from
		bonding
	where
		bonding.type = 2
			and bonding.shooter_id in (select
				`user2`
			from
				relation_table
			where
				`user1` = 192)
	group by bonding.global_id2
)
union
(
select
		bonding.type,
			bonding.global_id2 as target_post,
			bonding.target_id as on_whose_post,
			GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
			GROUP_CONCAT(bonding.what_global_id) as shooted_what,
			MAX(bonding.what_global_id) as last,
			'bonding' as flag
	from
		bonding
	where
		bonding.type = 5
			and bonding.shooter_id in (select
				`user2`
			from
				relation_table
			where
				`user1` = 192)
	group by bonding.global_id2
)
union
(
select
		bonding.type,
			bonding.global_id2 as target_post,
			bonding.target_id as on_whose_post,
			GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
			GROUP_CONCAT(bonding.what_global_id) as shooted_what,
			MAX(bonding.what_global_id) as last,
			'bonding' as flag
	from
		bonding
	where
		bonding.type = 9
			and bonding.shooter_id in (select
				`user2`
			from
				relation_table
			where
				`user1` = 192)
	group by bonding.global_id2
)
union
(
select
		bonding.type,
			bonding.global_id2 as target_post,
			bonding.target_id as on_whose_post,
			GROUP_CONCAT(bonding.shooter_id) as shooter_ids,
			GROUP_CONCAT(bonding.what_global_id) as shooted_what,
			MAX(bonding.what_global_id) as last,
			'bonding' as flag
	from
		bonding
	where
		bonding.type = 10
			and bonding.shooter_id in (select
				`user2`
			from
				relation_table
			where
				`user1` = 192)
	group by bonding.global_id2
)

)as t where events.global_id = t1.last

 

databse structure is given here

EVENTS-

 `global_id` int(11) NOT NULL AUTO_INCREMENT,
`event_creator` int(11) NOT NULL,
`event_type` int(2) NOT NULL,
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`global_id`)

 

bonding:-

`shooter_id` int(12) NOT NULL COMMENT 'id of person who did something',
`type` int(4) NOT NULL COMMENT '1:news share; 2:photo share; 5:comment; 10:red flag; 9:green flag',
`what_global_id` int(12) NOT NULL COMMENT 'global id of event which is done',
`global_id2` int(12) NOT NULL COMMENT 'global id on which it is done',
`target_id` int(12) NOT NULL

Edited November 25, 2012 by manishk3008

[fenway]

Sure -- post the EXPLAIN results of each part of the UNION separately.