Query performance improvements - collecting user_activities in CakePHP 3.0

Question

I'm currently working on a cakephp 3.0 project to create an individual social network. I'm dealing with a problem for my "timeline". There I have a query which first gets a list of all my friends inside this community and then collecting all the user_activities they made. (see query below).

So at the end I have a list with all my friends activities ordered by created... In the FrontEnd I use a foreach to loop through it and have for every "activity.type" a seperate view template (just a text-post, an image-post or a video-post)

For a number of 50-100 Friends its ok... the timeline needs < 1sec. to display properly ... but the community has users with up to 5000 friends (I tested with ~400 friends which had a query runtime of 4-5 seconds).

Do somebody has an idea how I could improve the query or could I do something else to improve the perfomance (only from SQL or PHP side,... I already set up some indexes for certain columns and thought about a limitation by date, e.x. collecting only activities from - 1 year).

I am not very experienced with this topic, so I would appreciate any ideas :)

-------- EDIT

gather friendlist

SELECT (CASE WHEN `Friends`.`user_id` = '6' THEN `FriendUsers`.`id` ELSE `Friends`.`user_id` END) AS `id` FROM `friends` `Friends` LEFT JOIN `users` `FriendUsers` ON `FriendUsers`.`id` = (`Friends`.`friend_id`) WHERE (`status` >= 0 AND (`Friends`.`user_id` = 6 OR `Friends`.`friend_id` = 6))

the query for collecting the user-activities

SELECT  `Wallposts`.`text` AS `Wallposts__text`, `Activities`.`name` AS `Activities__name`,
        `Users`.`firstname` AS `Users__firstname`, `Users`.`lastname` AS `Users__lastname`,
        `Users`.`username` AS `Users__username`, `Users`.`id` AS `Users__id`,
        `UsersActivities`.`created` AS `UsersActivities__created`,
        `UsersActivities`.`id` AS `UsersActivities__id`, `UsersActivities`.`video_id` AS `UsersActivities__video_id`,
        `UsersActivities`.`picture_id` AS `UsersActivities__picture_id`,
        `UsersActivities`.`visible_for_group` AS `UsersActivities__visible_for_group`,
        `Pictures`.`id` AS `Pictures__id`, `Pictures`.`user_id` AS `Pictures__user_id`,
        `Pictures`.`description` AS `Pictures__description`, `Pictures`.`fileName` AS `Pictures__fileName`,
        `Pictures`.`album_id` AS `Pictures__album_id`, `Pictures`.`isChosen` AS `Pictures__isChosen`,
        `Albums`.`id` AS `Albums__id`, `Albums`.`name` AS `Albums__name`,
        `Videos`.`id` AS `Videos__id`, `Videos`.`hoster` AS `Videos__hoster`,
        `Videos`.`video_key` AS `Videos__video_key`, `Videos`.`name` AS `Videos__name`,
        `Videos`.`description` AS `Videos__description`
    FROM  `users_activities` `UsersActivities`
    left JOIN  `users` `Users`  ON UsersActivities.user_id = Users.id
    left JOIN  `activities` `Activities`  ON UsersActivities.activity_id = Activities.id
    left JOIN  `wallposts` `Wallposts`  ON UsersActivities.wallpost_id = Wallposts.id
    LEFT JOIN  `videos` `Videos`  ON `Videos`.`id` = (`UsersActivities`.`video_id`)
    LEFT JOIN  `pictures` `Pictures`  ON `Pictures`.`id` = (`UsersActivities`.`picture_id`)
    LEFT JOIN  `albums` `Albums`  ON `Albums`.`id` = (`Pictures`.`album_id`)
    WHERE  (`UsersActivities`.`member_id` = 0
              AND  `Users`.`active` not in ('2')
              AND  `UsersActivities`.`user_activity_id` = 0
              AND  (
                    (`UsersActivities`.`user_id` = 390900002
                              AND  `UsersActivities`.`activity_id` not in (5,6)
                    )
                      OR  (`UsersActivities`.`user_id` in 
                                (391407850,391511765,
                                        391511432,491511714,391512398,391204138,391407984,391000522,
                                        391408687,391511708,391305910,391511812,391511681,491512107,
                                        391408047,391408494, -- and hundreds more 
                                )
                              AND  `UsersActivities`.`visible_for_group` in (0,3)
                          )
                   )
           )
    ORDER BY  `UsersActivities`.`created` desc 

EXPLAIN JSON FORMAT

{
  "query_block": {
    "select_id": 1,
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "UsersActivities",
            "access_type": "range",
            "possible_keys": [
              "user_id",
              "activity_id",
              "member_id",
              "user_activity_id",
              "visible_for_group_idx"
            ],
            "key": "user_id",
            "used_key_parts": [
              "user_id"
            ],
            "key_length": "8",
            "rows": 25623,
            "filtered": 100,
            "index_condition": "((`project`.`usersactivities`.`user_id` = 390900002) or (`project`.`usersactivities`.`user_id` in ( *[HUNDREDS OF FRIEND IDS]* )))",
            "attached_condition": "((`project`.`usersactivities`.`user_activity_id` = 0) and (`project`.`usersactivities`.`member_id` = 0) and (((`project`.`usersactivities`.`user_id` = 390900002) and (`project`.`usersactivities`.`activity_id` not in (5,6))) or ((`project`.`usersactivities`.`user_id` in ( *[HUNDRED OF FRIEND IDS]* )) and (`project`.`usersactivities`.`visible_for_group` in (0,3)))))"
          }
        },
        {
          "table": {
            "table_name": "Users",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "8",
            "ref": [
              "project.UsersActivities.user_id"
            ],
            "rows": 1,
            "filtered": 100,
            "attached_condition": "(`project`.`users`.`active` <> '2')"
          }
        },
        {
          "table": {
            "table_name": "Activities",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows": 6,
            "filtered": 83.333,
            "using_join_buffer": "Block Nested Loop",
            "attached_condition": "<if>(is_not_null_compl(Activities), (`project`.`activities`.`id` = `project`.`usersactivities`.`activity_id`), true)"
          }
        },
        {
          "table": {
            "table_name": "Wallposts",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.wallpost_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Videos",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.video_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Pictures",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.UsersActivities.picture_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        },
        {
          "table": {
            "table_name": "Albums",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "id"
            ],
            "key_length": "4",
            "ref": [
              "project.Pictures.album_id"
            ],
            "rows": 1,
            "filtered": 100
          }
        }
      ]
    }
  }
}

Answer 1

In the FrontEnd I use a foreach to loop

I stopped reading there. You should be able to make a single SQL query that locates all your friends, JOIN to their activities, and produces the entire list of activities, sorted by date.

Going back and forth to the databases is costly. Gathering, digesting, sorting, etc. all the desired data is usually faster when done in the database server and done in a single query.

For more help, we need to see both the "find friends" query and the "gather activities" query so we can help you put them together.

Some notes on the query you provided:

• OR prevents the use of indexes, at least for that part.
• NOT IN -- ditto.
• Don't use LEFT unless you are expecting the 'right' table to be messy. It makes it harder for us to understand what is going on, and to figure out what optimizations are / are not available.
• Will there be a LIMIT applied? It seems like this might produce a huge amount of data. Will there be pagination? Is CakePHP gathering a zillion rows, then parceling out a few? Bad news.

Suggested index:

UsersActivities:  (user_activity_id, member_id, created)

If you add that index, please provide the EXPLAIN so we can compare it to the current EXPLAIN.

(It looks like most JOINs are on id, which I assume is the PRIMARY KEY of their respective tables.)

Another possible improvement: Currently you have

SELECT ...
    `Activities`.`name` AS `Activities__name`,
    ...
  left JOIN  `activities` `Activities`
         ON UsersActivities.activity_id = Activities.id
    ...

and that seems to be the only reference to Activities. It seems that it is a 1:many mapping, so perhaps would be an improvement, both in speed and user-friendliness:

SELECT ...
    ( SELECT GROUP_CONCAT(DISTINCT `name`) FROM activities
        WHERE id = UsersActivities.activity_id
    )  AS `Activities__name`,
    ...
  -- And leave out left JOIN  `activities` 
    ...