Can anyone speed this query up? Slow SQL help!

[SJ77]

I'm only using a small part of this function actually but I wanted to post it all to provide the big picture. There is a part of the query in this function that finds recent attachments a user has posted to the forums. The block is on the user profile. IT works but the problem is ... it's VERY slow!! Core attachments locks up for 30+ seconds and makes my site unusable.

Any one who could help it would be much appreciated.

 

private function getAttImages($limit, $forumIds = 0, $fidsReverse = false, $topicIds = 0, $membersIds = 0, $order = 'attach_date', $sort = 'desc', $group = null)
	{
		$fids = '';
		
		if ($forumIds)
		{
			$r = '';
			if ($fidsReverse)
			{
				$r = ' NOT ';
			}
			
			if (is_array($forumIds))
			{
				$forumIds = implode(',', $forumIds);
			}
			
			$fids = ' AND forums_topics.forum_id ' . $r . ' IN (' . $forumIds .  ')';
		}

		$tids = '';
		if ($topicIds)
		{
			$tids = ' AND forums_topics.tid IN (' . $topicIds . ')';
		}
		
		$mids = '';
		if ($membersIds)
		{
			$mids = ' AND core_attachments.attach_member_id IN (' . $membersIds . ')';
		}

		
		$whereT = array();
		$joinsT = array();
	
		$findInPosts = ' AND ' . \IPS\Db::i()->findInSet('queued', array('0'));
		$joinsT[] = array(
							'select'	=> 'forums_posts.*',
							'from' 		=> 'forums_posts',
							'where' 	=> array("forums_posts.pid=core_attachments_map.id2" . $findInPosts),
					);
					
		$findInTopics = ' AND ' . \IPS\Db::i()->findInSet('approved', array('1'));
		$joinsT[] = array(
							'select'	=> 'forums_topics.*',
							'from' 		=> 'forums_topics',
							'where' 	=> array("forums_topics.tid=forums_posts.topic_id" . $findInTopics . $fids . $tids),
					);
		
		$select = 'core_attachments.attach_id AS custom_data, core_attachments.*';
		if ($group)
		{
			$select = 'core_attachments.attach_id AS custom_data, COUNT(attach_is_image) as cnt_images, SUM(attach_hits) as summ_attach_hits, core_attachments.*';
		}
		
		$joinsT[] = array(
							'select'	=> $select,
							'from' 		=> 'core_attachments',
							'where' 	=> array('core_attachments.attach_is_image=1 AND core_attachments.attach_is_archived=0 AND core_attachments.attach_id=core_attachments_map.attachment_id' . $mids),
					);				

		$joinsT[] = array( 	'select'	=> 'core_members.member_id, core_members.member_group_id, core_members.mgroup_others, core_members.name, core_members.members_seo_name',
							'from'  	=> 'core_members',
							'where' 	=> array('core_attachments.attach_member_id=core_members.member_id' . $mids),
					);
					
		$joinsT[] = array( 	'select'	=> 'core_permission_index.perm_id',
							'from'  	=> 'core_permission_index',
							'where' 	=> array("core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_topics.forum_id"),
					);

		$groupT = $group;
		
		$whereT[] = array(
			"core_attachments_map.location_key='forums_Forums' AND " . 
			\IPS\Db::i()->findInSet('perm_view', array_merge(array(\IPS\Member::loggedIn()->member_group_id), array_filter(explode(',', \IPS\Member::loggedIn()->mgroup_others)))) . " OR perm_view='*'" .
			$fids . $tids . $mids
		);

		$table = new \IPS\Helpers\Table\Db(
			'core_attachments_map',
			\IPS\Http\Url::internal('app=core&module=system&controller=nbattachpictures', 'front', 'nbattachpictures'),
			$whereT,
			$groupT
		);
	
		$table->joins = $joinsT;
		$table->limit = $limit;
		$table->sortBy = $order;
		$table->sortDirection = $sort;
	
		$table->rowsTemplate = array(\IPS\Theme::i()->getTemplate('plugins', 'core', 'global'), 'nbAttachmentsBlocksRows');
		
		$table->parsers = array(
			'custom_data' => function( $val, $row )
			{
				return array(
					'topic_data' => \IPS\Http\Url::internal("app=forums&module=forums&controller=topic&id={$row['tid']}", 'front', 'forums_topic', array($row['title_seo'])),
					'summ_attach_hits' => $row['summ_attach_hits'],
					'jewel' => $this->attachJewel($row['summ_attach_hits']),
				);
			},
		);
		
		return $table;
	}
	
	
	

 

[Luis Manson]

1 use "explain"

2 create indexes where applicable

http://dev.mysql.com/doc/en/explain.html