Opening the "home" timeline takes ages

[StCyr]

Describe the bug
Opening the "home" timeline takes ages (more than 10 seconds).

It seems to be caused by the SQL query used to retrieve the data.

To Reproduce

Here's the query that's effectively launched when I open the timeline. Running it from a mysql client takes more than 20 seconds to get the results:

SELECT DISTINCT `s`.`id`, `s`.`type`, `s`.`to`, `s`.`to_array`, `s`.`cc`, `s`.`bcc`, `s`.`content`, `s`.`summary`, `s`.`attachments`, `s`.`published`, `s`.`published_time`, `s`.`cache`, `s`.`object_id`, `s`.`attributed_to`, `s`.`in_reply_to`, `s`.`source`, `s`.`local`, `s`.`instances`, `s`.`creation`, `s`.`hidden_on_timeline`, `s`.`details`, `s`.`hashtags`, `f`.`object_id` AS `following_actor_id`, `ca`.`id` AS `cacheactor_id`, `ca`.`type` AS `cacheactor_type`, `ca`.`account` AS `cacheactor_account`, `ca`.`following` AS `cacheactor_following`, `ca`.`followers` AS `cacheactor_followers`, `ca`.`inbox` AS `cacheactor_inbox`, `ca`.`shared_inbox` AS `cacheactor_shared_inbox`, `ca`.`outbox` AS `cacheactor_outbox`, `ca`.`featured` AS `cacheactor_featured`, `ca`.`url` AS `cacheactor_url`, `ca`.`preferred_username` AS `cacheactor_preferred_username`, `ca`.`name` AS `cacheactor_name`, `ca`.`summary` AS `cacheactor_summary`, `ca`.`public_key` AS `cacheactor_public_key`, `ca`.`source` AS `cacheactor_source`, `ca`.`creation` AS `cacheactor_creation`, `ca`.`local` AS `cacheactor_local`, `sa`.`id` AS `streamaction_id`, `sa`.`actor_id` AS `streamaction_actor_id`, `sa`.`stream_id` AS `streamaction_stream_id`, `sa`.`values` AS `streamaction_values` FROM `oc_social_a2_stream` `s` LEFT JOIN `oc_social_a2_follows` `f` ON ((LOWER(`s`.`to`) = LOWER(`f`.`follow_id`)) OR (`s`.`to_array`  COLLATE utf8mb4_general_ci LIKE CONCAT('%\"', CONCAT(`f`.`follow_id`, '\"%'))) OR (`s`.`cc`  COLLATE utf8mb4_general_ci LIKE CONCAT('%\"', CONCAT(`f`.`follow_id`, '\"%'))) OR (`s`.`bcc`  COLLATE utf8mb4_general_ci LIKE CONCAT('%\"', CONCAT(`f`.`follow_id`, '\"%')))) AND (LOWER(`f`.`actor_id`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND (`f`.`accepted` = '1') LEFT JOIN `oc_social_a2_cache_actors` `ca` ON (LOWER(`f`.`object_id`) = LOWER(`ca`.`id`)) OR ((LOWER(`s`.`attributed_to`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND (LOWER(`s`.`attributed_to`) = LOWER(`ca`.`id`))) LEFT JOIN `oc_social_a2_stream_action` `sa` ON ((LOWER(`s`.`id`) = LOWER(`sa`.`stream_id`)) OR (LOWER(`s`.`object_id`) = LOWER(`sa`.`stream_id`))) AND (LOWER(`sa`.`actor_id`) = 'https://nextcloud.bollu.be/index.php/apps/social/@cyrille') LEFT JOIN `oc_social_a2_follows` `fs` ON (LOWER(`fs`.`actor_id`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND (LOWER(`s`.`attributed_to`) = LOWER(`fs`.`object_id`)) AND (`fs`.`accepted` = '1') WHERE (((LOWER(`s`.`attributed_to`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND (`s`.`cc` <> '[]')) OR (`f`.`object_id` IS NOT NULL)) AND (`s`.`published_time` <= '2019-09-16 09:32:46') AND ((`s`.`hidden_on_timeline` = '0') OR ((LOWER(`attributed_to`) <> LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND (`fs`.`id` IS NULL))) ORDER BY `s`.`published_time` desc LIMIT 25;

Expected behavior
Opening the "home" timeline shouldn't take more than 1 or 2 seconds.

Additional info
Here are some stat about my DB:

MariaDB [nextcloud]> SELECT TABLE_NAME, TABLE_ROWS FROM   `information_schema`.`tables`  WHERE  `table_schema` = 'nextcloud'        AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME like 'oc_social%';
+----------------------------+------------+
| TABLE_NAME                 | TABLE_ROWS |
+----------------------------+------------+
| oc_social_a2_request_queue |        678 |
| oc_social_a2_follows       |         45 |
| oc_social_a2_hashtags      |       1347 |
| oc_social_a2_stream_queue  |       2486 |
| oc_social_a2_stream        |      11326 |
| oc_social_a2_stream_action |         82 |
| oc_social_a2_cache_actors  |        802 |
| oc_social_a2_cache_documts |       4172 |
| oc_social_a2_actors        |          2 |
| oc_social_a2_actions       |       2520 |
+----------------------------+------------+
10 rows in set (0.001 sec)

[StCyr]

Here's the query better formated.

I guess the slownesses come from the LIKE CONCAT('%\"', CONCAT(f.follow_id, '\"%'))) statements.

And, indeed, transforming these statements to LIKE CONCAT('\"', CONCAT(f.follow_id, '\"%'))) (note the removed leading % makes the query run in less than 2 seconds.

Are these leading % really needed @daita ?

SELECT DISTINCT `s`.`id`, `s`.`type`, `s`.`to`, `s`.`to_array`, `s`.`cc`, `s`.`bcc`, `s`.`content`, `s`.`summary`, `s`.`attachments`, 
                `s`.`published`, `s`.`published_time`, `s`.`cache`, `s`.`object_id`, `s`.`attributed_to`, `s`.`in_reply_to`, `s`.`source`, 
                `s`.`local`, `s`.`instances`, `s`.`creation`, `s`.`hidden_on_timeline`, `s`.`details`, `s`.`hashtags`, `f`.`object_id` AS `following_actor_id`,
                `ca`.`id` AS `cacheactor_id`, `ca`.`type` AS `cacheactor_type`, `ca`.`account` AS `cacheactor_account`, `ca`.`following` AS `cacheactor_following`,
                `ca`.`followers` AS `cacheactor_followers`, `ca`.`inbox` AS `cacheactor_inbox`, `ca`.`shared_inbox` AS `cacheactor_shared_inbox`,
                `ca`.`outbox` AS `cacheactor_outbox`, `ca`.`featured` AS `cacheactor_featured`, `ca`.`url` AS `cacheactor_url`,
                `ca`.`preferred_username` AS `cacheactor_preferred_username`, `ca`.`name` AS `cacheactor_name`, `ca`.`summary` AS `cacheactor_summary`,
                `ca`.`public_key` AS `cacheactor_public_key`, `ca`.`source` AS `cacheactor_source`, `ca`.`creation` AS `cacheactor_creation`,
                `ca`.`local` AS `cacheactor_local`, `sa`.`id` AS `streamaction_id`, `sa`.`actor_id` AS `streamaction_actor_id`, `sa`.`stream_id` AS `streamaction_stream_id`,
                `sa`.`values` AS `streamaction_values` FROM `oc_social_a2_stream` `s` 
            LEFT JOIN `oc_social_a2_follows` `f` ON 
                ((LOWER(`s`.`to`) = LOWER(`f`.`follow_id`)) OR 
                (`s`.`to_array`  COLLATE utf8mb4_general_ci LIKE CONCAT('%\"', CONCAT(`f`.`follow_id`, '\"%'))) OR 
                (`s`.`cc`  COLLATE utf8mb4_general_ci LIKE CONCAT('%\"', CONCAT(`f`.`follow_id`, '\"%'))) OR 
                (`s`.`bcc`  COLLATE utf8mb4_general_ci LIKE CONCAT('%\"', CONCAT(`f`.`follow_id`, '\"%')))) AND 
                (LOWER(`f`.`actor_id`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND 
                (`f`.`accepted` = '1') 
            LEFT JOIN `oc_social_a2_cache_actors` `ca` ON 
                (LOWER(`f`.`object_id`) = LOWER(`ca`.`id`)) OR 
                ((LOWER(`s`.`attributed_to`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND 
                (LOWER(`s`.`attributed_to`) = LOWER(`ca`.`id`))) 
            LEFT JOIN `oc_social_a2_stream_action` `sa` ON 
                ((LOWER(`s`.`id`) = LOWER(`sa`.`stream_id`)) OR 
                (LOWER(`s`.`object_id`) = LOWER(`sa`.`stream_id`))) AND 
                (LOWER(`sa`.`actor_id`) = 'https://nextcloud.bollu.be/index.php/apps/social/@cyrille') 
            LEFT JOIN `oc_social_a2_follows` `fs` ON 
                (LOWER(`fs`.`actor_id`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND 
                (LOWER(`s`.`attributed_to`) = LOWER(`fs`.`object_id`)) AND 
                (`fs`.`accepted` = '1') 
            WHERE 
                (((LOWER(`s`.`attributed_to`) = LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND 
                (`s`.`cc` <> '[]')) OR 
                (`f`.`object_id` IS NOT NULL)) AND 
                (`s`.`published_time` <= '2019-09-16 09:32:46') AND 
                ((`s`.`hidden_on_timeline` = '0') OR
                ((LOWER(`attributed_to`) <> LOWER('https://nextcloud.bollu.be/index.php/apps/social/@cyrille')) AND 
                (`fs`.`id` IS NULL))) 
            ORDER BY `s`.`published_time` desc 
            LIMIT 25;

[ArtificialOwl]

yes, it is needed :-/

Is it slow also with the lazy loading when you scroll down ?

[StCyr]

Is it slow also with the lazy loading when you scroll down ?

The infinite-loader? yes, also slow

[StCyr]

solved by #752