This select statement takes more than 13 seconds to run and slows down our group profile access considerably. (This is the recent activity widget in groups) SELECT DISTINCT rv . * FROM elggriver rv JOIN elggentities e1 ON e1.guid = rv.object_guid LEFT JOIN elggentities e2 ON e2.guid = rv.target_guid JOIN elggentities oe ON rv.object_guid = oe.guid LEFT JOIN elggentities te ON rv.target_guid = te.guid WHERE ( e1.container_guid =272967 OR e2.container_guid =272967 ) AND rv.enabled = 'yes' AND ( ( 1 =1 ) AND ( oe.enabled = 'yes' ) ) AND ( ( ( 1 =1 ) AND ( te.enabled = 'yes' ) ) OR te.guid IS NULL ) ORDER BY rv.posted DESC LIMIT 0 , 4
If we remove DISTINCT and run the query from phpmyadmin or workbench it runs very fast.
Is there anything that we can do?
info@elgg.org
Security issues should be reported to security@elgg.org!
©2014 the Elgg Foundation
Elgg is a registered trademark of Thematic Networks.
Cover image by RaĆ¼l Utrera is used under Creative Commons license.
Icons by Flaticon and FontAwesome.
- Mathieu Blondin@mblondin
Mathieu Blondin - 0 likes
- ihayredinov@ihayredinov
ihayredinov - 0 likes
- Mathieu Blondin@mblondin
Mathieu Blondin - 0 likes
- Steve Clay@steve_clay
Steve Clay - 0 likes
You must log in to post replies.reason i ask is because even with distinct off in the core code the groups are slow because of the activity river widget
A common solution is to load slow views with ajax.
@Ismayil, I tested doing a view in phpmyadmin and workbench for the same query noted above and response time is considerably faster. So in a nutshell, I am wondering how to best replace
$content = elgg_list_river(array( 'limit' => 4, 'pagination' => false, 'joins' => array( "JOIN {$db_prefix}entities e1 ON e1.guid = rv.object_guid", "LEFT JOIN {$db_prefix}entities e2 ON e2.guid = rv.target_guid", ), 'wheres' => array( "(e1.container_guid = $group->guid OR e2.container_guid = $group->guid)", ), ));
with a call to my (Select) to my DB View.
thx for the help
You can pass in the distinct option to elgg_list_river(). You'd want to do this in your own plugin.