MyBB Community Forums

MyBB 1.4.4

Some of SELECT queries in online.php use DISTINCT when selecting or counting rows with sid. SID is a primary key so all values are distinct and so are selected rows.

Is is important? Yep:

SELECT DISTINCT s.sid, s.ip, s.uid, s.time, s.location, u.username, s.nopermission, u.invisible, u.usergroup, u.displaygroup
FROM mybb_sessions s
LEFT JOIN mybb_users u ON ( s.uid = u.uid ) 
WHERE s.time > '1234781254'
ORDER BY IF( s.uid >0, 1, 0 ) DESC , s.time

Query time: 0.05 - 0.06 seconds
Explain says: Using where; Using temporary; Using filesort

SELECT s.sid, s.ip, s.uid, s.time, s.location, u.username, s.nopermission, u.invisible, u.usergroup, u.displaygroup
FROM mybb_sessions s
LEFT JOIN mybb_users u ON ( s.uid = u.uid ) 
WHERE s.time > '1234781254'
ORDER BY IF( s.uid >0, 1, 0 ) DESC , s.time

Query time: 0.02 seconds
Explain says: Using where; Using filesort

Rows returned by this queries will be identical.

Other SQL with unneeded DISTINCT (online.php):

		case "sqlite3":
		case "sqlite2":	
			$query = $db->simple_select("sessions", "COUNT(count_sid)", "(SELECT DISTINCT sid as count_sid FROM ".TABLE_PREFIX."sessions WHERE time > {$timesearch})");
			break;
		case "pgsql":
		default:
			$query = $db->simple_select("sessions", "COUNT(DISTINCT sid) as online", "time > {$timesearch}");
			break;

inc\functions_search.php:

SELECT DISTINCT f.fid 
							FROM ".TABLE_PREFIX."forums f 

(fid is primary key)

usercp.php:

SELECT g.title, g.gid, g.type, COUNT(DISTINCT u.uid) AS users, COUNT(DISTINCT j.rid) AS joinrequests, l.canmanagerequests, l.canmanagemembers
				FROM ".TABLE_PREFIX."groupleaders l
				LEFT JOIN ".TABLE_PREFIX."usergroups g ON(g.gid=l.gid)
				LEFT JOIN ".TABLE_PREFIX."users u ON(((CONCAT(',', u.additionalgroups, ',') LIKE CONCAT('%,', g.gid, ',%')) OR u.usergroup = g.gid))

(uid and rid are primary keys)

SQLite doesn't have sid on a primary key for various reasons, so DISTINCT is still required for SQLite.