Query optimizations
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 :)
Friendica Developers reshared this.
Michael Vogel
in reply to Philipp Holzer • • •Roland Häder
in reply to Philipp Holzer • • •SELECTcommand with acodeblock around it? And if you want to see what MySQL/MariaDB does when it executesSELECTcommands, just put aEXPLAIN EXTENDEDin front of it. ThatEXTENDEDgives more details about what the query optimizer did.