[BUG 4.7.12] The table core_clubs needs more indexes, queries are slow with lots of clubs

[teraßyte]

On a site with a lot (really a lot) of clubs, the function \IPS\Member\Club::clubs() becomes rather slow because there are no indexes for the suggested columns in the phpDoc text:

	/**
	 * Get all clubs a member can see
	 *
	 * @param	\IPS\Member				$member		The member to base permission off or NULL for all clubs
	 * @param	int						$limit		Number to get
	 * @param	string					$sortOption	The sort option ('last_activity', 'members', 'content' or 'created')
	 * @param	bool|\IPS\Member|array	$mineOnly	Limit to clubs a particular member has joined (TRUE to use the same value as $member). Can also provide an array as array( 'member' => \IPS\Member, 'statuses' => array( STATUS_MEMBER... ) ) to limit to certain member statuses
	 * @param	array					$filters	Custom field filters
	 * @param	mixed					$extraWhere	Additional WHERE clause
	 * @param	bool					$countOnly	Only return a count, instead of an iterator
	 * @return	\IPS\Patterns\ActiveRecordIterator|array|int
	 */

 

The description for $sortOption mentions 4 columns (last_activity, members, content, created), but the only indexes available are created and members. The other columns, including name (which isn't even listed in phpDoc), have no index, and any query ordering results by those becomes slower the more clubs the site has.

Not to mention that there are queries based on club type, and the type column doesn't have an index either. And finally, there is an index on location_lat, but none on location_long. If you're trying to filter clubs based on location, you'd probably need an index on both columns, not just one. 🤨

 

We noticed the issue because the site has several Pages blocks that load clubs (2000+) based on various specific queries.

 

P.S.: It would also be nice to have the option to filter the clubs based on their type in case you want to load only public or open clubs only. But that's more feedback that a bug. Just something to consider if you end up updating the clubs() function. (Or maybe look into it for v5. 😉)

Edited July 23, 2023 by teraßyte

[Marc]

This issue has been resolved in our recent 4.7.14 release of the platform. Please upgrade to resolve the issue, and if you see any further problems at that point, please let us know.