-

Mon, 12 Jul 2021 16:50:35 +0200

adminsforum@hubzilla.org

Mark Nowiasz schrieb den folgenden Beitrag Mon, 12 Jul 2021 16:50:29 +0200

Query causing huge load on mysql

Hi,

I've noticed that occassionally a single query - always the same - causes a huge load ony the system, including iowait. It's always the same, the queries are taking more than one hour to run. I've posted the output of show full processlist\G below, the culprit - this time - is:

select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )

Any idea what the problem is? Is there an index missing or why are the queries running for mor than an hour?

Here's the output of mariadb's processlist:

*************************** 2. row ***************************
      Id: 310
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 3585
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 3. row ***************************
      Id: 545
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 3486
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 4. row ***************************
      Id: 1367
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 3284
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 5. row ***************************
      Id: 1662
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 3217
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 6. row ***************************
      Id: 2214
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2983
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 7. row ***************************
      Id: 2343
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2914
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 8. row ***************************
      Id: 2755
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2681
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 9. row ***************************
      Id: 3089
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2597
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 10. row ***************************
      Id: 3870
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2372
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 11. row ***************************
      Id: 4150
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2162
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 12. row ***************************
      Id: 4786
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 2066
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 13. row ***************************
      Id: 4984
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1967
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 14. row ***************************
      Id: 5213
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1885
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 15. row ***************************
      Id: 5371
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1740
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 16. row ***************************
      Id: 5531
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1659
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 17. row ***************************
      Id: 6037
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1431
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 18. row ***************************
      Id: 6116
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1329
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 19. row ***************************
      Id: 6448
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1129
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 20. row ***************************
      Id: 6717
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 1027
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 21. row ***************************
      Id: 7220
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 820
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 22. row ***************************
      Id: 7431
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 720
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 23. row ***************************
      Id: 8097
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 473
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 24. row ***************************
      Id: 8199
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 413
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 25. row ***************************
      Id: 8922
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 124
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000
*************************** 26. row ***************************
      Id: 8961
    User: hubzilla_netzgemeinde
    Host: localhost
      db: hub_netzgemeinde_eu
 Command: Query
    Time: 99
   State: Sending data
    Info: select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' )
Progress: 0.000

show all 15 comments

Tue, 13 Jul 2021 11:41:17 +0200

Hilmar Runge

hilmar@hub.netzgemeinde.eu

The affected columns are indexed:
The Explain performed with the mentioned Select on my much smaller hub is:

+------+-------------+---------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------+------+-------------------------------------------------+
| id   | select_type | table   | type        | possible_keys                                                                                                                                                                                                  | key                   | key_len | ref                 | rows | Extra                                           |
+------+-------------+---------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------+------+-------------------------------------------------+
|    1 | SIMPLE      | item    | index_merge | uid_commented,uid_created,uid_item_unseen,uid_item_type,uid_item_thread_top,uid_item_blocked,uid_item_wall,uid_item_starred,uid_item_retained,uid_item_private,uid_resource_type,parent_mid,uid_mid,thr_parent | thr_parent,parent_mid | 764,764 | NULL                |   86 | Using union(thr_parent,parent_mid); Using where |
|    1 | SIMPLE      | channel | eq_ref      | PRIMARY                                                                                                                                                                                                        | PRIMARY               | 4       | em.uid              |    1 |                                                 |
+------+-------------+---------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+---------+---------------------+------+-------------------------------------------------+

While the result from the Select is:
48 rows in set (0.00 sec)

Mark, when you vary the mentioned Select with Exlain (ie: explain select ...) via mysql cli, the output may show more especially with the sure much higher amount of data from your hub.

But that all may not be the true issue. I will try to have a look in the code where perhaps an outer loop in php together with the series of Selects can cause the problem.

Tue, 13 Jul 2021 21:36:13 +0200

Hilmar Runge

hilmar@hub.netzgemeinde.eu

After my 1st sniff into the zotlib code found in Zotlabs/Lib/Libzot.php line #1434

$z = q("select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = '%s' OR item.parent_mid = '%s' ) ",
                    dbesc($thread_parent),
                    dbesc($thread_parent)
                );

and looking around, I believe it's worth to try instead (with distinct):

$z = q("select distinct channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = '%s' OR item.parent_mid = '%s' ) ",
                    dbesc($thread_parent),
                    dbesc($thread_parent)
                );

because duplicates are sure not required at this point.
But
@Mario Vavti
what do you think?

Wed, 14 Jul 2021 00:36:53 +0200

Mario Vavti

mario@hub.somaton.com

@Hilmar Runge channel hashes should be unique anyway. Not sure if distinct will improve anything here...

Wed, 14 Jul 2021 00:58:40 +0200

Mario Vavti

mario@hub.somaton.com

@Hilmar Runge oh, i see what you mean... The hashes will be multiplied due to the items...
Well, this will require some testing since distinct or group by can take longer to process by SQL allthough the result set will be much smaller...

Basically the returned values are being deduplicated later in the process. It would be good to find out which way is faster...

In general i don't think this is related to @Mark Nowiasz's issue. An hour ore even more is insane for this query. No matter if the results are distinct or not...

@Mark Nowiasz please check if the indices at your installation are fine. Also, is it possible that another SQL process is keeping this process from finishing sooner?

The result of explain for this query could also be useful as already mentioned by @Hilmar R.

Wed, 14 Jul 2021 01:19:07 +0200

Mario Vavti

mario@hub.somaton.com

@Hilmar Runge another possibility to get distinct values would be something like that:

select channel_hash as hash from channel where channel_id in (select uid from item WHERE ( thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' ));

Wed, 14 Jul 2021 07:25:48 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Mario Vavti Yes, all indexes seems to be in order -

explain select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' );

says:

+------+-------------+---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+----------------------------------------+------+-------------+
| id   | select_type | table   | type  | possible_keys

        | key          | key_len | ref                                    | rows | Extra       |
+------+-------------+---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+----------------------------------------+------+-------------+
|    1 | SIMPLE      | channel | index | PRIMARY

        | channel_hash | 764     | NULL                                   | 450  | Using index |
|    1 | SIMPLE      | item    | ref   | uid_commented,uid_created,uid_item_unseen,uid_item_type,uid_item_thread_top,uid_item_blocked,uid_item_wall,uid_item_starred,uid_item_retained,uid_item_private,uid_resource_type,parent_mid,thr_parent,uid_mid | uid_mid      | 4       | hub_netzgemeinde_eu.channel.channel_id | 24   | Using where |
+------+-------------+---------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+----------------------------------------+------+-------------+

I don't see any other SQL process blocking these queries - they are the only ones running at this time. If this would be the case these processes whould have the stat "Waiting for table lock" or something similar. Furthermore, these queries cause system load - especially IO wait, so there's somtehing going on.

I've reduced the problem by increasing join_buffer_size, after I adjusted it there's now only one of these processes running (instead of 5 or so, causing a load of 20+). This is a little bit odd, because the query uses indexes and shouldn't be affected by join_buffer_size..

Wed, 14 Jul 2021 09:46:14 +0200 zuletzt bearbeitet: Wed, 14 Jul 2021 09:48:16 +0200

Mario Vavti

mario@hub.somaton.com

@Mark Nowiasz hm it uses the index uid_mid for some reason. That is maybe not the best index to use for this query...

Wed, 14 Jul 2021 15:11:06 +0200

Hilmar Runge

hilmar@hub.netzgemeinde.eu

When I call

select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685');

the rs has 48 rows and needs 0.06s on my small hub.
Using distinct

select distinct channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685');

the rs has 1 row in 0.01s
Similar is with subquery:

select channel_hash as hash from channel where channel_id in (select uid from item WHERE ( thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' ));

with rs 1 row in 0.01s

But the subquery needs a virtual temporary table for the intermediate rs. Thus, distinct may be the choice (to see the three examples with explain).

And distinct will be a match, because when following the code in zotlib the results in the array will set to unique to discart any dups.

@Mark Nowiasz when you perform the three selects on your very much bigger hub, please let us know the number of rows and durations.

Wed, 14 Jul 2021 15:27:27 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

@Hilmar Runge Will do. I'm running them in a tmux session, because they take such long time. I've started with the distinct-query, currently running for 234 seconds now, so this might take some time to complete all three queries (one by one, because they take some toll on the server, especially IO wait and load)

Wed, 14 Jul 2021 15:47:38 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

The distinct-Query now finished:
48 rows in set (23 min 8.234 sec)

Now let's test the next one...

Wed, 14 Jul 2021 15:49:13 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

That was fast.

select channel_hash as hash from channel where channel_id in (select uid from item WHERE ( thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' ));

:

48 rows in set (0.032 sec)

Wed, 14 Jul 2021 16:54:18 +0200

Hilmar Runge

hilmar@hub.netzgemeinde.eu

Thanks, that says a lot for to change to Mario's subselect solution.

A lot of details are to find here: https://stackoverflow.com/questions/2577174/join-vs-sub-query

Wed, 14 Jul 2021 17:07:02 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

Finally, the "original" query (

select channel_hash as hash from channel left join item on channel.channel_id = item.uid where ( item.thr_parent = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685' OR item.parent_mid = 'https://hub.netzgemeinde.eu/item/e84de8c4-aba5-44d2-a911-b1812b087685');

:

2259 rows in set (1 hour 7 min 1.136 sec)

Please note that it results in more rows than the other one - however, as far as I can see there are lots of duplicate entries, so the 48 results are probably correct:

| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |
| __7yzrj9Xw_uYRQ2ZkBA1O43vR5vauZCx9MkRfdoUVGuIbtmOn7AVnOmrov4TqC-rTPxqrLadtD1pLYWdr3PoQ |

Some facts about the database and the tables:

select count(*) from item;
+----------+
| count(*) |
+----------+
|  7549964 |
+----------+

The database's size ist about 188 GB, the RDBMS is mariadb (10.5.10).

Wed, 14 Jul 2021 17:32:41 +0200

Mario Vavti

mario@hub.somaton.com

In my opnion the only problem is that the optimzer goes for the wrong index at this point of time (it might be completely different after some days pass and the data set changes). Sometimes it helps to make the query slightly more complicated than necessary to bring the optimizer back to the ground. Maybe using the original query with an additional (pointless) sort by clause (order by uid) would also work. Since we want to get rid of the duplicates anyway, i would suggest to go with the subquery though.

Wed, 14 Jul 2021 18:06:59 +0200

Mark Nowiasz

buckaroo@hub.netzgemeinde.eu

It would be wise to test the new query on a large hub using PostgreSQL so the change won't have a negative impact on sites using this rdbms