[Jorandh]

I'm currently optimising my database. Basically my whole Slow_query_log is filled with the queries from your plugin. Any chance you can improve the queries and use of left joins without indexes?

Example:

SET timestamp=1427964576;
SELECT
				thread.*,
				post.*,
				user.*,
				thread.title AS threadtitle,
				thread.dateline,
				thread.threadid AS contentid,
				'threads' AS contenttypeid,
				favourite.userid IS NOT NULL AS isfavourite				
				, avatar.avatarpath, NOT ISNULL(customavatar.userid) AS hascustomavatar, customavatar.dateline AS avatardateline, customavatar.width AS avwidth, customavatar.height AS avheight, customavatar.height_thumb AS avheight_thumb, customavatar.width_thumb AS avwidth_thumb, customavatar.filedata_thumb
			FROM (SELECT threadid FROM vb_thread WHERE threadid > 215606 ORDER BY threadid DESC LIMIT 500) AS tmp
			LEFT JOIN vb_thread AS thread ON(thread.threadid = tmp.threadid)
			LEFT JOIN vb_post AS post ON(post.postid = thread.firstpostid)
			LEFT JOIN vb_user AS user ON(user.userid = thread.postuserid)
			LEFT JOIN vb_dbtech_livewall_settings AS currentuser_settings ON(currentuser_settings.userid = 71492)
			LEFT JOIN vb_dbtech_livewall_settings AS actionuser_settings ON(actionuser_settings.userid = user.userid)
			LEFT JOIN vb_dbtech_livewall_favourite AS favourite ON(favourite.userid = 71492 AND favourite.contenttypeid = 'threads' AND favourite.contentid = thread.threadid)
			LEFT JOIN vb_userlist AS actionuser_friend ON(actionuser_friend.userid = user.userid AND actionuser_friend.relationid = 71492)
			LEFT JOIN vb_avatar AS avatar ON (avatar.avatarid = user.avatarid) LEFT JOIN vb_customavatar AS customavatar ON (customavatar.userid = user.userid)
			WHERE NOT ISNULL(thread.threadid)
				AND post.visible = 1
				AND thread.visible = 1
				AND thread.open != 10
				AND thread.forumid NOT IN(0,12,181,186,384,32,387,229,218,219,263,249,230,134,319,353,388,318,65,271,373,272,306,220,270,360,402,70,56,59,311,63,356,321,298,11,286,278,159,40,41,385)
				AND NOT FIND_IN_SET(0, membergroupids)
				AND usergroupid NOT IN('0')
				
				AND
				(
					user.userid = 71492 OR
					(
						(
							ISNULL(currentuser_settings.threads_display) OR 
							(
								currentuser_settings.threads_display = 0 OR
								(
									currentuser_settings.threads_display = 1 AND
									71492 > 0
								) OR
								(
									currentuser_settings.threads_display = 2 AND
									actionuser_friend.type = 'buddy'
								) OR
								(
									currentuser_settings.threads_display = 3 AND
									actionuser_friend.type = 'buddy' AND
									actionuser_friend.friend = 'yes'
								)
							)
						) AND
						(
							ISNULL(actionuser_settings.threads_privacy) OR 
							(
								actionuser_settings.threads_privacy = 0 OR
								(
									actionuser_settings.threads_privacy = 1 AND
									71492 > 0
								) OR
								(
									actionuser_settings.threads_privacy = 2 AND
									actionuser_friend.type = 'buddy'
								) OR
								(
									actionuser_settings.threads_privacy = 3 AND
									actionuser_friend.type = 'buddy' AND
									actionuser_friend.friend = 'yes'
								)
							)
						)						
					)
				)
				
			ORDER BY tmp.threadid DESC;

Which comes from /dbtech/livewall/threads.php


And it's just one example, there are multiple queries in different contenttype queries.