How to improve the performance of loading dashboard page


We are developing a site which we have one page call "news feeds" like facebook "news feeds".

In my implementation, I used follow snippet code to get the news feeds via the river table:

$activity = elgg_list_river(array(
    'joins' => array("JOIN {$db_prefix}entities object ON object.guid = rv.object_guid"),
    'wheres' => array("rv.object_guid IN (SELECT guid_two FROM {$db_prefix}entity_relationships WHERE guid_one=$user->guid AND relationship='enroll')
        OR rv.subject_guid = $user->guid
        OR rv.subject_guid IN (SELECT guid_two FROM {$db_prefix}entity_relationships WHERE guid_one=$user->guid OR guid_two=$user->guid AND relationship='classmate')
        OR rv.subject_guid IN (SELECT guid_one FROM {$db_prefix}entity_relationships WHERE guid_two=$user->guid OR guid_two=$user->guid AND relationship='friend')"),
    "pagination" => false));

 I got total time of loading time to process it about 4 seconds.
My data set is ten users who have ten status objects and each status object  have ten annotation comments.

When i commentted above snippet code. I got the total loading time is about 100ms second. I wonder how can I  improve the loading speed or there are any other way to implement the dashboard's news feed.

  • I would try to change subqueries to left joins.

    Apart from that I would extract SQL query you're running here and run EXPLAIN on it.

  • Sorry but I can't understand clear your sentence "I would try to change subqueries to left joins".

    When I look into my mysql log. Elgg will call 

    SELECT DISTINCT rv.* FROM elgg_river rv JOIN elgg_entities object ON object.guid = rv.object_guid WHERE rv.object_guid IN (SELECT guid_two FROM elgg_entity_relationships WHERE guid_one=1551 AND relationship='enroll')
    OR rv.subject_guid = 1551
    OR rv.subject_guid IN (SELECT guid_two FROM elgg_entity_relationships WHERE guid_one=1551 OR guid_two=1551 AND relationship='classmate')
    OR rv.subject_guid IN (SELECT guid_one FROM elgg_entity_relationships WHERE guid_two=1551 OR guid_two=1551 AND relationship='friend') AND ( (rv.access_id = -2
    AND rv.subject_guid IN (
    SELECT guid_one FROM elgg_entity_relationships
    WHERE relationship='friend' AND guid_two=1551
    )) OR (rv.access_id IN (2,1)
    OR (rv.subject_guid = 1551)
    OR (
    rv.access_id = 0
    AND rv.subject_guid = 1551
    ) ) ORDER BY rv.posted desc LIMIT 0, 20;

    And when I run "EXPLAIN" command there are one point that I concern is 

    Using where; Using temporary; Using filesort 

    Using where; Using index; Distinct 

    I don't know how to improve. My team plan will add one or two more tables to remove JOINS

Performance and Scalability

Performance and Scalability

If you've got a need for speed, this group is for you.