Friendica: Community page: initial loading super slow - DB query running for 1.5 minutes
- [X] I have searched open and closed issues for duplicates
Bug Description
The current DB stucture in develop is producing some very slow queries. The one that is extremely slow is the query for loading the content of the community pages. It takes approx. 1.5 - 2 minutes until the page is completely loaded.
Subsequent pages (content) loaded via infinite scroll are pretty fast. When I scroll down the next items are loaded in approx. 1 second.
The query:
# Thread_id: 613292 Schema: friendica QC_hit: No
# Query_time: 85.822263 Lock_time: 0.000050 Rows_sent: 25 Rows_examined: 10194724
# Rows_affected: 0 Bytes_sent: 1815
# Tmp_tables: 8 Tmp_disk_tables: 0 Tmp_table_sizes: 1269880
# Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SET timestamp=1612607182;
SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `moderated` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `hidden` AND `uri-id` = `post-thread-view`.`uri-id` AND `uid` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
Explain the query:
MariaDB [friendica]> explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `moderated` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `hidden` AND `uri-id` = `post-thread-view`.`uri-id` AND `uid` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------+----------+-------------+
| 1 | PRIMARY | post-thread | index | PRIMARY,owner-id,author-id,causer-id | commented | 5 | NULL | 25 | Using where |
| 1 | PRIMARY | post-thread-user | eq_ref | PRIMARY,uid_wall,uid_pinned,uri-id | PRIMARY | 7 | const,friendica.post-thread.uri-id | 1 | Using index |
| 1 | PRIMARY | post-user | ref | uri-id,contact-id | uri-id | 4 | friendica.post-thread.uri-id | 1 | |
| 1 | PRIMARY | item | ref | parent,uid_id,deleted_changed,uid_wall_changed,uid_eventid,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_commented,uid_thrparent,uid_parenturi,uid_uri,uid_received,uid_contactid_received,uri-id,uid_unseen_wall,causer-id | uri-id | 5 | friendica.post-thread.uri-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | friendica.post-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-item | eq_ref | PRIMARY,authorid_received | PRIMARY | 4 | friendica.item.parent | 1 | |
| 1 | PRIMARY | parent-item-author | eq_ref | PRIMARY | PRIMARY | 4 | friendica.parent-item.author-id | 1 | Using index |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | ref | uid_uri-id,uri-id | uid_uri-id | 3 | const | 13342874 | Using where |
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------+----------+-------------+
Steps to Reproduce
- try to go to the (global) community page
- experience the extreme slowness
- have a look at you slow queries log file
Actual Result:
Initial page loading takes 1.5 - 2 minutes
Expected Result:
Page should be loaded in 1.5 - 2 seconds :-)
Platform Info
Friendica Version:
2021.03-dev - dbstructure 1398/1398 - postupdate 1384/1384
Friendica Source:
git
PHP version:
7.4
SQL version:
10.3 MariaDB
AlfredSK
All 66 comments
After 'mysqloptimize' the query is faster but still slow.
AlfredSK
on 6 Feb 2021
Weird. On my machine, the same explain command tells this:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+------------------------------------+
| 1 | PRIMARY | post-thread | index | PRIMARY,owner-id,author-id,causer-id | commented | 5 | NULL | 25 | Using where |
| 1 | PRIMARY | post-thread-user | eq_ref | PRIMARY,uid_wall,uri-id,uid_pinned | PRIMARY | 7 | const,piratica.post-thread.uri-id | 1 | Using index |
| 1 | PRIMARY | post-user | ref | uri-id,contact-id | uri-id | 4 | piratica.post-thread.uri-id | 1 | |
| 1 | PRIMARY | item | ref | parent,deleted_changed,uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall,causer-id | uri-id | 5 | piratica.post-thread.uri-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_name,uid_rel_network_poll,uid_network_batch,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | piratica.post-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-item | eq_ref | PRIMARY,authorid_received | PRIMARY | 4 | piratica.item.parent | 1 | |
| 1 | PRIMARY | parent-item-author | eq_ref | PRIMARY | PRIMARY | 4 | piratica.parent-item.author-id | 1 | Using index |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | post-user | eq_ref | uid_uri-id,uri-id | uid_uri-id | 7 | const,func | 1 | Using index condition; Using where |
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+------------------------------------+
See the difference in the last row. I don't know why our systems react differently. I guess I have to dig deeper into MySQL ...
annando
on 9 Feb 2021
I can check this this evening.
Hmm. Interesting. ref vs. eq_ref in the type column.
eq_ref is what you want because it's faster on getting the rows you're looking for.
AlfredSK
on 9 Feb 2021
I have some adjustments in my config to allow the optimization of deeper subqueries. Maybe that's the reason for the difference. But I cannot check this now.
Btw. what version of MariaDB do you use?
AlfredSK
on 9 Feb 2021
I'm using MariaDB 10.1.45. Do you know more about this eq_ref and ref thing?
annando
on 9 Feb 2021
I'm on MariaDB 10.3.x.
Do you know more about this eq_ref and ref thing?
Only what I could find on the documentation pages of MariaDB. For example...
https://mariadb.com/kb/en/explain/
https://mariadb.com/kb/en/building-the-best-index-for-a-given-select/
AlfredSK
on 9 Feb 2021
@AlfredSK thanks for the documentation! I guess I have to have a look at it. BTW: Can you check with the latest change? I hope that this will help. My system is really fast with it.
annando
on 9 Feb 2021
Really fast? Yes, I'll check immediately. :-)
I will then have a look at the mysql slow log to see how the other slow queries are behaving now. There are some other one's related to the post-user table.
AlfredSK
on 9 Feb 2021
On venera.social the community page is loading in 3 seconds now. Promising. :-) The update on libranet.de takes more time...
AlfredSK
on 9 Feb 2021
Community page is faster now. Thanks. :-)
AlfredSK
on 9 Feb 2021
What does explain say?
annando
on 9 Feb 2021
Explain:
MariaDB [friendica]> explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `moderated` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `hidden` AND `uri-id` = `post-thread-view`.`uri-id` AND `uid` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------+------+-----------------------+
| 1 | PRIMARY | post-thread | index | PRIMARY,owner-id,author-id,causer-id | commented | 5 | NULL | 25 | Using where |
| 1 | PRIMARY | post-thread-user | eq_ref | PRIMARY,uid_wall,uid_pinned,uri-id,contact-id | PRIMARY | 7 | const,friendica.post-thread.uri-id | 1 | |
| 1 | PRIMARY | item | ref | parent,uid_id,deleted_changed,uid_wall_changed,uid_eventid,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_commented,uid_thrparent,uid_parenturi,uid_uri,uid_received,uid_contactid_received,uri-id,uid_unseen_wall,causer-id | uri-id | 5 | friendica.post-thread.uri-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | friendica.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-item | eq_ref | PRIMARY,authorid_received | PRIMARY | 4 | friendica.item.parent | 1 | |
| 1 | PRIMARY | parent-item-author | eq_ref | PRIMARY | PRIMARY | 4 | friendica.parent-item.author-id | 1 | Using index |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | range | uid_uri-id,uri-id,uid_hidden | uid_hidden | 4 | NULL | 2 | Using index condition |
+------+--------------------+--------------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------+------+-----------------------+
Okay. This looks good. There will be a PR in in the future that will reduce the number of tables in these queries.
This is how the same query currently looks like:
+------+--------------------+------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+-----------------------+
| 1 | PRIMARY | post-thread | index | PRIMARY,owner-id,author-id | commented | 5 | NULL | 25 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | piratica.post-thread.causer-id | 1 | |
| 1 | PRIMARY | post-thread-user | eq_ref | PRIMARY,uid_wall,uri-id,uid_pinned,contact-id | PRIMARY | 7 | const,piratica.post-thread.uri-id | 1 | |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_name,uid_rel_network_poll,uid_network_batch,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | piratica.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | item | ref | deleted_changed,uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall,causer-id | uri-id | 5 | piratica.post-thread.uri-id | 1 | Using where |
| 1 | PRIMARY | parent-item | ref | uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall | uri-id | 5 | piratica.item.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | range | uid_uri-id,uri-id,uid_hidden | uid_hidden | 4 | NULL | 2 | Using index condition |
+------+--------------------+------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+-----------------------+
Simpler queries now lead to a massive reduced set of tables:
> explain select `uri-id` FROM `post-thread-view` LIMIT 25;
+------+-------------+------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+-----------------------------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+-----------------------------+---------+-------------+
| 1 | SIMPLE | post-thread | ALL | PRIMARY | NULL | NULL | NULL | 1017540 | |
| 1 | SIMPLE | post-thread-user | ref | PRIMARY,uid_wall,uri-id,uid_pinned | uri-id | 4 | piratica.post-thread.uri-id | 1 | |
| 1 | SIMPLE | item | ref | uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall | uri-id | 5 | piratica.post-thread.uri-id | 1 | Using where |
| 1 | SIMPLE | parent-item | ref | uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall | uri-id | 5 | piratica.item.parent-uri-id | 1 | Using where |
+------+-------------+------------------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+-----------------------------+---------+-------------+
I currently work on getting rid of that parent-item as well. But currently my time is really limited, so progress may have to wait until the weekend.
annando
on 10 Feb 2021
This really looks like MariaDB 10.1 and 10.3 have different query optimizer algorithms. There is still a difference. But now it is good nonetheless.
AlfredSK
on 10 Feb 2021
One of the latest changes has slowed down the community page again. It's the initial loading of the page and also the subsequent pages with infinite scroll that are slow now.
# Thread_id: 3158934 Schema: friendica QC_hit: No
# Query_time: 15.900041 Lock_time: 0.000062 Rows_sent: 25 Rows_examined: 8501291
# Rows_affected: 0 Bytes_sent: 1659
SET timestamp=1615238183;
SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 0 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
Explain:
MariaDB [friendica]> explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 0 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+--------------------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+--------------------------------------------+----------+-------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,post-user-id,owner-id,causer-id,uid_commented,author-id,uid,uid_mention,uid_received,uid_starred | uid_commented | 3 | const | 4949784 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | friendica.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,friendica.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | ref | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen | uid_unseen_contactid | 3 | const | 15190200 | Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+--------------------------------------------+----------+-------------+
Is this info sufficient or should I open a new issue?
AlfredSK
on 8 Mar 2021
I will have a look at it.
annando
on 8 Mar 2021
Everything else is fast?
annando
on 9 Mar 2021
Yep. Seems so.
AlfredSK
on 9 Mar 2021
The same explain looks here this way:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-------------------------------------------+--------+------------------------------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,owner-id,causer-id,uid_commented,author-id,uid,post-user-id,uid_received,uid_mention,uid_starred | uid_commented | 3 | const | 875129 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | piratica.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_name,uid_rel_network_poll,uid_network_batch,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | piratica.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | piratica.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,piratica.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | post-user | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,parent-uri-id_uid,author-id_uid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,func | 1 | Using index condition; Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-------------------------------------------+--------+------------------------------------+
What does this query returns:
explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` WHERE `uid` = 1 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 1 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
(The uid values had been replaced)
annando
on 9 Mar 2021
Geez. I'm in a train with only a phone... Wait...
AlfredSK
on 9 Mar 2021
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+--------------------------------------------+---------+-------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,post-user-id,owner-id,causer-id,uid_commented,author-id,uid,uid_mention,uid_received,uid_starred | uid_commented | 3 | const | 4944326 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | friendica.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,friendica.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | ref | uid_uri-id,uri-id,parent-uri-id_uid,author-id_uid,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 3 | const | 141 | Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+--------------------------------------------+---------+-------------+
Uh. Something went wrong with copy and paste. Hope it's okay.
AlfredSK
on 9 Mar 2021
Your system is weird ... I'm now adding some indexes on my system for further testing. Possibly I will need your assistance as well. Means: You could add some indexes and then check for their usage. But at first I should get up, get a shower and then ride to work and get a breakfast ...
annando
on 9 Mar 2021
Please try again after the PR #10014 got merged. And please have a look at your slow query log for other weird occurrences as well. And now I guess I can ride to work (through the snow).
annando
on 9 Mar 2021
I will do what you say as soon as I'm home. In approx. one hour.
AlfredSK
on 9 Mar 2021
Your system is weird
I can only guess that the query optimizer of MariaDB 10.1 and 10.3 behaves differently.
AlfredSK
on 9 Mar 2021
I pulled. Loading of community page still takes ~17 seconds.
# Query_time: 14.859788 Lock_time: 0.000063 Rows_sent: 25 Rows_examined: 8502452
# Rows_affected: 0 Bytes_sent: 1724
SET timestamp=1615310401;
SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` USE INDEX (`uid_uri-id`) WHERE `uid` = 0 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
Explain:
MariaDB [friendica]> explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` USE INDEX (`uid_uri-id`) WHERE `uid` = 0 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+--------------------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+--------------------------------------------+----------+-------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,post-user-id,owner-id,causer-id,uid_commented,author-id,uid,uid_mention,uid_received,uid_starred | uid_commented | 3 | const | 4966852 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | friendica.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | friendica.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | friendica.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,friendica.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,friendica.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | ref | uid_uri-id | uid_uri-id | 3 | const | 16665942 | Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+--------------------------------------------+----------+-------------+
The only other two queries I have in my slow log are these...
As far as I remember the first one is a non-critical background task:
# Query_time: 15.530981 Lock_time: 0.000019 Rows_sent: 2 Rows_examined: 34289065
# Rows_affected: 0 Bytes_sent: 96
SET timestamp=1615310688;
SELECT `id` FROM `contact` WHERE (`network` IN ('dfrn', 'dspr', 'stat', 'apub', 'zot!', 'unkn') AND `self` = 0 AND `id` IN (SELECT `owner-id` FROM `post-user`) AND (`last-update` < '2021-02-09 17:24:33' OR (NOT `failed` AND `last-update` < '2021-03-02 17:24:33'))) LIMIT 100;
And this one where I don't know what it is doing:
# Query_time: 8.683537 Lock_time: 0.000018 Rows_sent: 38 Rows_examined: 879446
# Rows_affected: 0 Bytes_sent: 1209
SET timestamp=1615310761;
SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 596 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 596;
Explain:
MariaDB [friendica]> explain SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 596 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 596;
+------+--------------------+--------------+------+---------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+--------------+------+---------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+-------------+
| 1 | PRIMARY | group | ref | uid | uid | 3 | const | 38 | |
| 2 | DEPENDENT SUBQUERY | group_member | ref | gid_contactid,contactid | gid_contactid | 4 | friendica.group.id | 17 | Using index |
| 2 | DEPENDENT SUBQUERY | post-user | ref | uid_uri-id,contact-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_contactid | 7 | const,friendica.group_member.contact-id | 14 | Using where |
+------+--------------------+--------------+------+---------------------------------------------------------------------+---------------+---------+-----------------------------------------+------+-------------+
Weird. I tried the same query (community page) on the forum node. That server has the same MariaDB version. But is has smaller buffers and caches in MariaDB's config because it has much less memory. Query is ultra fast and explain looks different...
Community page query on the forum node:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+----------------------------------------+------+------------------------------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,post-user-id,owner-id,causer-id,uid_commented,author-id,uid,uid_mention,uid_received,uid_starred | uid_commented | 3 | const | 3680 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | forum.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | forum.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | forum.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | forum.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | forum.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,forum.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,forum.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,forum.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | post-user | eq_ref | uid_uri-id | uid_uri-id | 7 | const,func | 1 | Using index condition; Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+----------------------------------------+------+------------------------------------+
Weird. Even when I remove all differences between the configs of the two MariaDB instances (except for the buffer pool size) EXPLAIN gives different results. I'm puzzled.
Of cause the machines are different: a VPS with 4 cores and a root server with 6 cores / 12 threads - I know there are some settings MariaDB automatically adjusts depending on the hardware it is running on.
AlfredSK
on 9 Mar 2021
You could try to optimize the tables.
annando
on 9 Mar 2021
Yeah. Mysqltuner suggests to optimize post-thread-user; -- can free 789 MB. But I don't think that will help with the different query plan MariaDB selects. But I will try it.
AlfredSK
on 9 Mar 2021
Thing is there are three Friendica instances on the same machine running on the same database server. Even the smallest one is producing the same EXPLAIN for that slow query. I will start to optimize that small DB and see what happens.
AlfredSK
on 9 Mar 2021
Ahaaaaa! After optimizing the mordor DB...
MariaDB [mordor]> explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` USE INDEX (`uid_uri-id`) WHERE `uid` = 0 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+-------+------------------------------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,post-user-id,owner-id,causer-id,uid_commented,author-id,uid,uid_mention,uid_received,uid_starred | uid_commented | 3 | const | 47073 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | mordor.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_name,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | mordor.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | mordor.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | mordor.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | mordor.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,mordor.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,mordor.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,mordor.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | post-user | eq_ref | uid_uri-id | uid_uri-id | 7 | const,func | 1 | Using index condition; Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+-------+------------------------------------+
Same machine. Same DB server. Another Friendica instance: venera.social - after DB optimization... :-/
MariaDB [venera]> explain SELECT `uri-id`, `commented`, `author-link` FROM `post-thread-user-view` WHERE (`uid` = 0 AND `private` = 0 AND `visible` AND NOT `deleted` AND NOT `author-blocked` AND NOT `owner-blocked` AND (NOT `causer-blocked` OR `causer-id` = 0 OR `causer-id` IS NULL) AND NOT `contact-blocked` AND ((NOT `contact-readonly` AND NOT `contact-pending` AND (`contact-rel` IN (2, 3))) OR `self` OR `gravity` != 0 OR `contact-uid` = 0) AND NOT EXISTS (SELECT `uri-id` FROM `post-user` USE INDEX (`uid_uri-id`) WHERE `uid` = 0 AND `uri-id` = `post-thread-user-view`.`uri-id` AND `hidden`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `blocked`) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `author-id` AND `ignored` AND `gravity` = 0) AND NOT EXISTS (SELECT `cid` FROM `user-contact` WHERE `uid` = 0 AND `cid` = `owner-id` AND `ignored` AND `gravity` = 0)) ORDER BY `commented` DESC LIMIT 25;
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+---------+-------------+
| 1 | PRIMARY | post-thread-user | ref | PRIMARY,uid_pinned,contact-id,post-user-id,owner-id,causer-id,uid_commented,author-id,uid,uid_mention,uid_received,uid_starred | uid_commented | 3 | const | 1136652 | Using where |
| 1 | PRIMARY | post-user | eq_ref | PRIMARY | PRIMARY | 4 | venera.post-thread-user.post-user-id | 1 | Using where |
| 1 | PRIMARY | contact | eq_ref | PRIMARY,uid_name,self_uid,pending_uid,blocked_uid,uid_rel_network_poll,uid_network_batch,uid_network_self_lastupdate,uid_lastitem | PRIMARY | 4 | venera.post-thread-user.contact-id | 1 | Using where |
| 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | venera.post-thread-user.author-id | 1 | Using where |
| 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | venera.post-thread-user.owner-id | 1 | Using where |
| 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | venera.post-thread-user.causer-id | 1 | Using where |
| 1 | PRIMARY | parent-post | eq_ref | uid_uri-id,uri-id,uid_unseen_contactid,uid_contactid,uid_unseen | uid_uri-id | 7 | const,venera.post-user.parent-uri-id | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,venera.post-thread-user.owner-id | 1 | Using where |
| 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,venera.post-thread-user.author-id | 1 | Using where |
| 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where |
| 2 | MATERIALIZED | post-user | ref | uid_uri-id | uid_uri-id | 3 | const | 3621966 | Using where |
+------+--------------------+------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------+---------------+---------+-----------------------------------------+---------+-------------+
That's really weird ... I do have a last work around that we can test. After this I'm really clueless.
annando
on 9 Mar 2021
Please create these indexes for testing purposes:
ALTER IGNORE TABLE `post-user` ADD INDEX `hidden_uid_uri-id` (`hidden`,`uid`,`uri-id`), ADD INDEX `hidden_uri-id_uid` (`hidden`,`uri-id`,`uid`), ADD INDEX `uri-id_hidden_uid` (`uri-id`,`hidden`,`uid`), ADD INDEX `uri-id_uid_hidden` (`uri-id`,`uid`,`hidden`), ADD INDEX `uid_uri-id_hidden` (`uid`,`uri-id`,`hidden`), ADD INDEX `uid_hidden_uri-id` (`uid`,`hidden`,`uri-id`);
Then perform some tests for some time and have a look for the index usage. I would like to see which of these indexes had been used how often.
You can check it with this query when you had activated it:
select OBJECT_NAME, INDEX_NAME, COUNT_star,count_read,count_write,count_fetch,count_insert,count_update,count_delete from performance_schema.table_io_waits_summary_by_index_usage where object_schema = 'your-database-name' and object_name = "post-user" order by object_name, count_star;
Ok. So I add those indices, let it run for some time and then post the result of that query you provided. Right?
AlfredSK
on 9 Mar 2021
Have you activated the index usage statistics already?
annando
on 9 Mar 2021
No idea. How?
AlfredSK
on 9 Mar 2021
I guess it is done this way: https://mariadb.com/kb/en/performance-schema-overview/#enabling-the-performance-schema
annando
on 9 Mar 2021
Is it thiis?
userstat = 1
AlfredSK
on 9 Mar 2021
Performance schema is already ON but I think this isn't sufficient for index stats.
By default statistics are not collected. This is to ensure that statistics collection does not cause any extra load on the server unless desired.
Set the userstat=ON system variable in a relevant server option group in an option file to enable the plugin.
AlfredSK
on 9 Mar 2021
Btw. after optimizing libranet.de the EXPLAIN ist ok. So...
mordor.social ... ok
venera.social ... not ok
libranet.de ... ok
AlfredSK
on 9 Mar 2021
W/o changing anything on my DB config I get this...
ariaDB [venera]> select OBJECT_NAME, INDEX_NAME, COUNT_star,count_read,count_write,count_fetch,count_insert,count_update,count_delete from performance_schema.table_io_waits_summary_by_index_usage where object_schema = 'venera' and object_name = "post-user" order by object_name, count_star;
+-------------+----------------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_NAME | INDEX_NAME | COUNT_star | count_read | count_write | count_fetch | count_insert | count_update | count_delete |
+-------------+----------------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| post-user | author-id_received | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | hidden_uri-id_uid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uri-id_hidden_uid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | vid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | thr-parent-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | hidden_uid_uri-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | event-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | psid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uid_unseen | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uid_uri-id_hidden | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uid_hidden_uri-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uri-id_uid_hidden | 3 | 3 | 0 | 3 | 0 | 0 | 0 |
| post-user | external-id | 18 | 18 | 0 | 18 | 0 | 0 | 0 |
| post-user | parent-uri-id | 19 | 19 | 0 | 19 | 0 | 0 | 0 |
| post-user | contact-id | 3576 | 3576 | 0 | 3576 | 0 | 0 | 0 |
| post-user | NULL | 5646 | 4497 | 1149 | 4497 | 1149 | 0 | 0 |
| post-user | PRIMARY | 5960 | 5850 | 110 | 5850 | 0 | 110 | 0 |
| post-user | parent-uri-id_uid | 17539 | 17539 | 0 | 17539 | 0 | 0 | 0 |
| post-user | author-id_uid | 52691 | 52691 | 0 | 52691 | 0 | 0 | 0 |
| post-user | uid_unseen_contactid | 88223 | 88223 | 0 | 88223 | 0 | 0 | 0 |
| post-user | uid_contactid | 199524 | 199524 | 0 | 199524 | 0 | 0 | 0 |
| post-user | uid_uri-id | 2054456 | 2054445 | 11 | 2054445 | 0 | 11 | 0 |
| post-user | uri-id | 7369071 | 7369071 | 0 | 7369071 | 0 | 0 | 0 |
| post-user | author-id | 43716993 | 43716993 | 0 | 43716993 | 0 | 0 | 0 |
| post-user | owner-id | 43717066 | 43717066 | 0 | 43717066 | 0 | 0 | 0 |
| post-user | causer-id | 43717086 | 43717086 | 0 | 43717086 | 0 | 0 | 0 |
+-------------+----------------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
26 rows in set (0.051 sec)
Is this the stuff you want?
AlfredSK
on 9 Mar 2021
Yeah, exactly. Let's wait for some time. And of course please have a look at the server that currently has got the problems (btw: does the new index fix something?)
annando
on 9 Mar 2021
It's weird. I added the new indices to venera.social's post-user table and libranet.de's post-user table - the two nodes with the issue.
venera.social is still not ok. Explain still showing an unexpected result. It's wrong but consistent. :-)
libranet.de is completely weird. When I run the query with explain I get different results for the same query. Ok, not ok, not ok, ok, ok, not ok, ok. At the moment it is good again...
Edit: Sorry. As time flies. Now explain is showing the slow version again. It is changing all the time.
AlfredSK
on 9 Mar 2021
Ah, I just realized why the new index isn't used ... We now have an index hint in the code. Please remove this part from src/Model/Post.php:
USE INDEX (`uid_uri-id`)
Btw. should I restart the DB server to get 'fresh' stats? Or are the stats persistent and a restart doesn't flush them?
AlfredSK
on 9 Mar 2021
You don't need to restart the DB server.
annando
on 9 Mar 2021
So? What does the system tells you, after the "use index" had been removed?
annando
on 9 Mar 2021
Do you mean the stats? :-) I thought it should run for a while... Or do you mean the output of explain?
AlfredSK
on 9 Mar 2021
I mean the stats. I would like to see how it develops and if it fits my own tests.
annando
on 9 Mar 2021
Ok. Same DB w/o 'use index ...' in Post.php:
MariaDB [venera]> select OBJECT_NAME, INDEX_NAME, COUNT_star,count_read,count_write,count_fetch,count_insert,count_update,count_delete from performance_schema.table_io_waits_summary_by_index_usage where object_schema = 'venera' and object_name = "post-user" order by object_name, count_star;
+-------------+----------------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_NAME | INDEX_NAME | COUNT_star | count_read | count_write | count_fetch | count_insert | count_update | count_delete |
+-------------+----------------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| post-user | author-id_received | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uri-id_hidden_uid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | hidden_uid_uri-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | thr-parent-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | event-id | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | psid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | vid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uid_uri-id_hidden | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uid_unseen | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | hidden_uri-id_uid | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| post-user | uri-id_uid_hidden | 4 | 4 | 0 | 4 | 0 | 0 | 0 |
| post-user | external-id | 51 | 51 | 0 | 51 | 0 | 0 | 0 |
| post-user | parent-uri-id | 722 | 722 | 0 | 722 | 0 | 0 | 0 |
| post-user | contact-id | 17126 | 17126 | 0 | 17126 | 0 | 0 | 0 |
| post-user | uid_hidden_uri-id | 20916 | 20916 | 0 | 20916 | 0 | 0 | 0 |
| post-user | NULL | 22463 | 19345 | 3118 | 19345 | 3118 | 0 | 0 |
| post-user | PRIMARY | 27044 | 22239 | 4805 | 22239 | 0 | 4805 | 0 |
| post-user | parent-uri-id_uid | 83792 | 83792 | 0 | 83792 | 0 | 0 | 0 |
| post-user | author-id_uid | 146693 | 146693 | 0 | 146693 | 0 | 0 | 0 |
| post-user | uid_unseen_contactid | 258655 | 258655 | 0 | 258655 | 0 | 0 | 0 |
| post-user | uid_contactid | 539138 | 539138 | 0 | 539138 | 0 | 0 | 0 |
| post-user | uri-id | 7382406 | 7382406 | 0 | 7382406 | 0 | 0 | 0 |
| post-user | uid_uri-id | 14540859 | 14540841 | 18 | 14540841 | 0 | 18 | 0 |
| post-user | author-id | 109304131 | 109304131 | 0 | 109304131 | 0 | 0 | 0 |
| post-user | causer-id | 109304339 | 109304339 | 0 | 109304339 | 0 | 0 | 0 |
| post-user | owner-id | 109305081 | 109305081 | 0 | 109305081 | 0 | 0 | 0 |
+-------------+----------------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
26 rows in set (0.046 sec)
Okay, this "uid_hidden_uri-id" is the one that is mostly used of these indexes on my machine as well. Is the system faster with them?
annando
on 9 Mar 2021
Yep. Feels snappier. Community page loads in less than 2 seconds now. No slow query logged (threshold is 8 seconds).
AlfredSK
on 9 Mar 2021
The community page on squeet.me loads in around 0.5 seconds :grin:
annando
on 9 Mar 2021
Well, I don't measure it I just estimate the time by looking at the page. :-) I think the initial loading is even faster but some content (images etc.) need more time to load.
AlfredSK
on 9 Mar 2021
You may try yourself: https://libranet.de/community or https://venera.social/community
Could also be related to the storage backend. I use filesystem.
AlfredSK
on 9 Mar 2021
I need some sleep now. 8 hours working for my employer and 5 hours working for you* ;-) is enough for today. Hahaha. :-)
*) for us, actually
AlfredSK
on 9 Mar 2021
Btw. does the next dbstructure update remove the added test indices or do I have to run a dbstructure update -f?
AlfredSK
on 9 Mar 2021
It will automatically remove the indexes. But you possibly have to revert the manual changes in the Post.php.
annando
on 9 Mar 2021
But you possibly have to revert the manual changes in the Post.php.
Yep. Thanks.
AlfredSK
on 9 Mar 2021
Does it now work on all of your systems?
annando
on 10 Mar 2021
The output of 'explain' is still different from yours. But the community page is fast now no matter what 'explain' I get.
mordor.social and forum.frienci.ca - explain identical to yours
libranet.de and venera.social - the other ;-) explain but no slow query anymore
Community page is loading fast.
Any idea about the second query in this comment? https://github.com/friendica/friendica/issues/9906#issuecomment-794207033
AlfredSK
on 10 Mar 2021
@AlfredSK the first query is the one that will take some time - and the second one shouldn't take this time. Possibly the system had been busy at this time. Please have a look if this repeats.
annando
on 10 Mar 2021
Yes. Not that often but on a regular basis. It occurs with different user IDs. If only I knew how to reproduce this.
Just now...
# Time: 210310 19:46:23
# Query_time: 10.286405 Lock_time: 0.000022 Rows_sent: 4 Rows_examined: 958703
# Rows_affected: 0 Bytes_sent: 248
SET timestamp=1615405583;
SELECT `group`.`id`, `group`.`name`, (SELECT COUNT(*) FROM `post-user` WHERE `uid` = 2121 AND `unseen` AND `contact-id` IN (SELECT `contact-id` FROM `group_member` WHERE `group_member`.`gid` = `group`.`id`) ) AS `count` FROM `group` WHERE `group`.`uid` = 2121;
Related issues
utzer
路
67Comments
MrPetovan
路
52Comments
AlfredSK
路
47Comments
AlfredSK
路
107Comments
clacke
路
76Comments
Most helpful comment
Okay. This looks good. There will be a PR in in the future that will reduce the number of tables in these queries.
This is how the same query currently looks like:
+------+--------------------+------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+-----------------------+ | 1 | PRIMARY | post-thread | index | PRIMARY,owner-id,author-id | commented | 5 | NULL | 25 | Using where | | 1 | PRIMARY | author | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.author-id | 1 | Using where | | 1 | PRIMARY | owner | eq_ref | PRIMARY,blocked_uid | PRIMARY | 4 | piratica.post-thread.owner-id | 1 | Using where | | 1 | PRIMARY | causer | eq_ref | PRIMARY | PRIMARY | 4 | piratica.post-thread.causer-id | 1 | | | 1 | PRIMARY | post-thread-user | eq_ref | PRIMARY,uid_wall,uri-id,uid_pinned,contact-id | PRIMARY | 7 | const,piratica.post-thread.uri-id | 1 | | | 1 | PRIMARY | contact | eq_ref | PRIMARY,self_uid,pending_uid,blocked_uid,uid_name,uid_rel_network_poll,uid_network_batch,uid_lastitem,uid_network_self_lastupdate | PRIMARY | 4 | piratica.post-thread-user.contact-id | 1 | Using where | | 1 | PRIMARY | item | ref | deleted_changed,uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall,causer-id | uri-id | 5 | piratica.post-thread.uri-id | 1 | Using where | | 1 | PRIMARY | parent-item | ref | uid_wall_changed,uid_eventid,uid_id,uid_unseen_contactid,uid_contactid_id,uid_network_received,uid_network_commented,uid_thrparent,uid_parenturi,uid_uri,uid_commented,uid_received,uid_contactid_received,uri-id,uid_unseen_wall | uri-id | 5 | piratica.item.parent-uri-id | 1 | Using where | | 6 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread.owner-id | 1 | Using where | | 5 | DEPENDENT SUBQUERY | user-contact | eq_ref | PRIMARY,cid | PRIMARY | 7 | const,piratica.post-thread.author-id | 1 | Using where | | 4 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where | | 3 | MATERIALIZED | user-contact | ref | PRIMARY,cid | PRIMARY | 3 | const | 1 | Using where | | 2 | MATERIALIZED | post-user | range | uid_uri-id,uri-id,uid_hidden | uid_hidden | 4 | NULL | 2 | Using index condition | +------+--------------------+------------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+--------------------------------------+------+-----------------------+Simpler queries now lead to a massive reduced set of tables:
I currently work on getting rid of that
parent-itemas well. But currently my time is really limited, so progress may have to wait until the weekend.