Post AY8AY4Ek8sSu7ggMsa by dottorblaster@fosstodon.org
 (DIR) More posts by dottorblaster@fosstodon.org
 (DIR) Post #AY8AQeGGJuM9XBr5No by Codeberg@social.anoxinon.de
       2023-07-27T20:27:04Z
       
       0 likes, 1 repeats
       
       Any #MariaDB experts not in the meeting, who want to help us investigate this: https://codeberg.org/Codeberg-Infrastructure/techstack-support/issues/9 ?
       
 (DIR) Post #AY8AY4Ek8sSu7ggMsa by dottorblaster@fosstodon.org
       2023-07-27T20:28:57Z
       
       0 likes, 0 repeats
       
       @Codeberg boosting for visibility
       
 (DIR) Post #AY8CZGEnD0Y7XaP0am by mtparet@famille.social
       2023-07-27T20:51:33Z
       
       0 likes, 0 repeats
       
       @Codeberg any lock time information? What are the others queries running at the same time ?
       
 (DIR) Post #AY8Cnx3Gp7dINf1Abw by bitpirate@mas.to
       2023-07-27T20:54:05Z
       
       0 likes, 0 repeats
       
       @Codeberg i wonder if an EXISTS predicate would be faster.
       
 (DIR) Post #AY8DAx3wwtTr4iIqeG by zechy@mastodonczech.cz
       2023-07-27T20:56:19Z
       
       0 likes, 0 repeats
       
       @Codeberg One time, we were solving something similar on MS SQL. And we just found out, that WHERE IN with subqueries are slow in general. Rewriting the query with joins greatly increased the performance.
       
 (DIR) Post #AY8DXRMwx0y9RJuhnc by truls46@mastodon.social
       2023-07-27T20:59:07Z
       
       0 likes, 0 repeats
       
       @Codeberg Well, MySQL/MariaDB is known to be notoriously slow with sub-queries using IN. It's probably better to run the sub-query first, store the IDs in a variable, then run the DELETE providing constant values (or upgrade to PostgreSQL 🤭)
       
 (DIR) Post #AY8F70dPvTLqu58lmK by n4ppo@floss.social
       2023-07-27T21:20:05Z
       
       0 likes, 0 repeats
       
       @Codeberg I don't know why this is happening, but I recon it might be connected to using a subquery in the IN part.I think deleting from a JOIN may have a chance to perform better:DELETE action FROM action INNER JOIN comment ON action.comment_id = comment.id WHERE comment.issue_id = 350251;
       
 (DIR) Post #AYASJ6AYGkPsiarEqO by Codeberg@social.anoxinon.de
       2023-07-28T22:57:15Z
       
       0 likes, 0 repeats
       
       @mtparet This specific problem was reproducible on a copy of the live data, so no other queries involved.