Outline Loads Slow

My outline started loading slow overnight.  Clicking on the more button there is a long delay before the outline contents is refreshed; didn't use to be this way, apparently overnight.

I have looked at a lot of areas; gone through my server with a comb; checking log files etc and don't see anything wrong. 

Of course Boonex decided to make the wall module do several things at once. Trying to look at the source code is confusing.  I would uninstall the module and clear it all out but then my members will want my head.

Any ideas where I should look?  The error console reported nothing.  Nothing showing in the log files either.

Hello geek_girl!

So what was changed in your server / site for this time? Maybe some number of new members? Also, the antivirus check will be good too.

It is just the outline block itself.  Nothing has changed.  It went from loading normally to loading slow.  I don't think it is the size of the table; I think I would have noticed a gradual slow down; not normal to taking nine seconds to load.  It could be a particular entry that was added so I will examine the table records.

If you want to outline the algorithm for the wall module, that would be nice.

Explain the fields in the table bx_wall_events.

For example, I see type, action, and content.  Then on some I see type profile, action edit, and then content, which for some is larger than what can be displayed in the database tool.  Why is profile edits being stored in the bx_wall_events table?  This may be the problem that I am experiencing because I allow rich content to be added to one's profile page.  Knowing more about this module and the database tables.  If I do a query and remove all records where type equals profile; will it cause any harm?  Of course I would need to edit the module to prevent theses types from being added.  What is the purpose of storing edits to a profile in the table?

Also, break down the code in the content field.

John was talking about a user manual but we need a programmers manual as well.

After doing more tests, I found that the issue is related to privacy on the site.  Content that has privacy set to members will load fast, content set to public loads slow.  This affects the outline because the outline is checking privacy; so it takes longer to generate the block.  Now, why would this issue just appear?  What would be causing this?  Could it be the social sharing that Public content contains?

I was wrong on the privacy things.  I enabled slow sql log and so far I am seeing the query for the wall module.  Maybe the table has just grown too large; it has been running for years although I don't have a lot of active members.  I guess I could purge some records and see what happens.

 That's an excellent comment. How long should stuff remain accessible via the wall. I'd be delighted if it lasted no longer than a month - even less. As people will have different views, maybe an admin setting to select a purge time would be good for a future upgrade?

Just a guess - try to check if wall have records with links, wall tries to load images for links from original sites (if protocols doesn't match) so if external site responses slowly it could cause wall to load slowly.

 All the time; they post links to memes and gifs and such on a constant basis.

Checking the slow log I see only the outline query:

Is 722519 a lot of rows for this query?

# Time: 190208 17:48:55
# User@Host: root[root] @ localhost []
# Thread_id: 251640  Schema: XXXXX  QC_hit: No
# Query_time: 6.736813  Lock_time: 0.000140  Rows_sent: 16  Rows_examined: 722519
SET timestamp=1549648135;
SELECT
                `te`.`id` AS `id`,
                `te`.`owner_id` AS `owner_id`,
                `te`.`object_id` AS `object_id`,
                `te`.`type` AS `type`,
                `te`.`action` AS `action`,
                `te`.`content` AS `content`,
                `te`.`title` AS `title`,
                `te`.`description` AS `description`,
                `te`.`date` AS `date`,
                DATE_FORMAT(FROM_UNIXTIME(`te`.`date`), '%d.%m.%Y') AS `print_date`,
                DAYOFYEAR(FROM_UNIXTIME(`te`.`date`)) AS `days`,
                DAYOFYEAR(NOW()) AS `today`,
                (UNIX_TIMESTAMP() - `te`.`date`) AS `ago`,
                ROUND((UNIX_TIMESTAMP() - `te`.`date`)/86400) AS `ago_days`
            FROM `bx_wall_events` AS `te`
            LEFT JOIN `bx_wall_handlers` AS `th` ON `te`.`type`=`th`.`alert_unit` AND `te`.`action`=`th`.`alert_action` LEFT JOIN `Profiles` AS `tp` ON `te`.`owner_id`=`tp`.`ID`
            WHERE 1 AND `tp`.`Status`='Active' AND `th`.`outline`='1' AND `th`.`id` NOT IN ('29')  ORDER BY `te`.`date` DESC LIMIT 0, 16;

John, I checked Pruning and there is no setting for the wall module; I have records going back to the time the site was open.

Try to add index on `date` field, then check if it will improve performance, let us know about the result:

ALTER TABLE  `bx_wall_events` ADD INDEX (  `date` )

I decided to do some table pruning.  I ran a query and deleted all rows of type = profile.  It seems that the settings in admin just hide things from presentation and not from being added to the table.  I don't care if someone edited their profile; nor do my members.  I think the table had just grown so large over the years that the query just took time to run.

722519 rows is a lot.  I run my own server and I have optimised MariaDB.  Of course having the database on its own server would improve things as well.

Your change may have improve the Outline query but the outline is back to normal now after pruning the table.  On very busy sites, and mine isn't, I can see the bx_wall_event table getting out of control.  Members probably don't need things in that table going back several years. 

Thanks for your help Alex.

Surely site members don't need stuff going back several months? It sounds like the endless page on facebook which I guess is their wall. I never did understand the logic of facebook and I can't understand the logic of a wall on Dolphin going back to the dark ages.  In all honesty, who would want to go back more than a couple of pages? Most people I know are only concerned about the activity on the front page.

Other than that, I think the wall and 'outline' are great.

Dolphin's database is already filled with unnecessary garbage going back to the days when the script was owned by someone else. Yet here we are, spending so much energy and presumably money, creating a completely new script when the company should have been refining this one.