Skip to main content

Query optimizations

!Friendica Developers

Hi,

Since I'm running one of the larger nodes (opensocial.at, friendica.me), I'm constantly looking for bottlenecks and improvements.

I log all slow queries for later analysis. Would it be helpful to post it anywhere with some insights of the database (how much data is in it, how many entries post-user or contact do have, ...)

For example this query:
SELECT `circle`.`id`, `circle`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 1137 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` AS `circle_member` WHERE `circle_member`.`gid` = `circle`.`id`) ) AS `count` FROM `group`
AS `circle` WHERE `circle`.`uid` = 1137
takes around 17 seconds and is constantly logged in the slow-query logs.

Shall I post it here, is it even useful? shall I open an issue for such cases? Is it just a problem for the node, so I post it at Friendica Admins for help?

@Michael Vogel what do you think :)

@Michael Vogel !Friendica Developers

reshared this

in reply to Philipp Holzer

Michael Vogel
Michael Vogel
• •
There is a setting where you can disable the counts for unseen posts in circles. I guess that we have to change something fundamental there, means not only a better query, but some changes changes to the data model.
in reply to Philipp Holzer

Roland Häder
Roland Häder
•
@Philipp Holzer @Michael Vogel Can you please wrap your SELECT command with a code block around it? And if you want to see what MySQL/MariaDB does when it executes SELECT commands, just put a EXPLAIN EXTENDED in front of it. That EXTENDED gives more details about what the query optimizer did.
@Michael Vogel @Philipp Holzer

Friendica Developers reshared this.

⇧