Skip to content

Peformance: Threads (+ Microblog) frontpage query #1869

@melroy89

Description

@melroy89

Describe the bug

In the threads frontpage (aka entry front controller), we use findByCriteria:

['results' => $this->contentRepository->findByCriteria($criteria), 'magazine' => $magazine],

Which is located here:

public function findByCriteria(Criteria $criteria): PagerfantaInterface

That function is creating a MASSIVE query.. Eventually resulting in something like this somehow:

SELECT c.id, 'entry' as type, c.type as content_type, c.created_at, c.ranking, c.score, c.comment_count, c.sticky, c.last_active FROM entry c
            LEFT JOIN magazine m ON c.magazine_id = m.id
            LEFT JOIN domain d ON c.domain_id = d.id
            INNER JOIN "user" u ON c.user_id = u.id
            WHERE (c.user_id = 1 OR m.id IN (1,4,8,9,10,15,17,18,21,22,23,24,25,26,27,28,29,30,32,33,35,37,38,40,41,42,45,46,47,48,52,53,54,55,57,58,59,60,61,65,67,69,71,72,73,77,79,80,82,83,84,85,86,88,92,94,95,96,97,98,99,100,101,102,103,104,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,123,124,125,127,128,130,132,133,134,137,138,139,140,141,142,143,145,146,147,148,149,150,151,152,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,178,179,180,181,182,183,184,185,186,187,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,259,262,264,266,268,269,270,271,272,273,274,275,277,278,279,280,282,283,285,286,288,289,290,293,294,295,296,297,298,299,300,301,303,304,305,306,311,313,314,315,316,317,320,323,324,325,327,330,331,332,335,336,337,338,339,340,343,346,347,349,350,352,353,354,355,358,359,360,361,362,363,374,376,377,379,383,523,664,873,1138,1141,2966,3019,3047,3487,18765) OR u.id IN (829,10841,6102,6449,20325,5382,8892,2269,16744,3383,28778,2369,1603,19383,4228,61304,62477,19660,131116,54282,121484,384,15680,127780,158577,589312,118854,567315,64565,1036802,248293,177875,43253,229427) OR d.id IN (-1)) AND (u.id NOT IN (941365,202476,149876,573500,1028867,1079014,959832,181410,175499,1178801) AND (m IS NULL OR m.id NOT IN (512,21594)) AND (d IS NULL OR d.id NOT IN (-1))) AND (m.visibility = 'visible') AND (u.visibility = 'visible') AND (c.visibility = 'visible' OR (c.visibility = 'private' AND u.id IN (829,10841,6102,6449,20325,5382,8892,2269,16744,3383,28778,2369,1603,19383,4228,61304,62477,19660,131116,54282,121484,384,15680,127780,158577,589312,118854,567315,64565,1036802,248293,177875,43253,229427))) AND (u.is_deleted = false) UNION ALL SELECT c.id, 'post' as type, 'microblog' as content_type, c.created_at, c.ranking, c.score, c.comment_count, c.sticky, c.last_active FROM post c
            LEFT JOIN magazine m ON c.magazine_id = m.id
            INNER JOIN "user" u ON c.user_id = u.id
            WHERE (c.user_id = 1 OR m.id IN (1,4,8,9,10,15,17,18,21,22,23,24,25,26,27,28,29,30,32,33,35,37,38,40,41,42,45,46,47,48,52,53,54,55,57,58,59,60,61,65,67,69,71,72,73,77,79,80,82,83,84,85,86,88,92,94,95,96,97,98,99,100,101,102,103,104,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,123,124,125,127,128,130,132,133,134,137,138,139,140,141,142,143,145,146,147,148,149,150,151,152,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,178,179,180,181,182,183,184,185,186,187,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,259,262,264,266,268,269,270,271,272,273,274,275,277,278,279,280,282,283,285,286,288,289,290,293,294,295,296,297,298,299,300,301,303,304,305,306,311,313,314,315,316,317,320,323,324,325,327,330,331,332,335,336,337,338,339,340,343,346,347,349,350,352,353,354,355,358,359,360,361,362,363,374,376,377,379,383,523,664,873,1138,1141,2966,3019,3047,3487,18765) OR u.id IN (829,10841,6102,6449,20325,5382,8892,2269,16744,3383,28778,2369,1603,19383,4228,61304,62477,19660,131116,54282,121484,384,15680,127780,158577,589312,118854,567315,64565,1036802,248293,177875,43253,229427)) AND (u.id NOT IN (941365,202476,149876,573500,1028867,1079014,959832,181410,175499,1178801) AND (m IS NULL OR m.id NOT IN (512,21594))) AND (m.visibility = 'visible') AND (u.visibility = 'visible') AND (c.visibility = 'visible' OR (c.visibility = 'private' AND u.id IN (829,10841,6102,6449,20325,5382,8892,2269,16744,3383,28778,2369,1603,19383,4228,61304,62477,19660,131116,54282,121484,384,15680,127780,158577,589312,118854,567315,64565,1036802,248293,177875,43253,229427))) AND (u.is_deleted = false) ORDER BY ranking DESC, created_at DESC LIMIT 25 OFFSET 0;

Here a formatted query from above:

SELECT
  c.id,
  'entry' as type,
  c.type as content_type,
  c.created_at,
  c.ranking,
  c.score,
  c.comment_count,
  c.sticky,
  c.last_active
FROM
  entry c
  LEFT JOIN magazine m ON c.magazine_id = m.id
  LEFT JOIN domain d ON c.domain_id = d.id
  INNER JOIN "user" u ON c.user_id = u.id
WHERE
  (
    c.user_id = :loggedInUser
    OR m.id IN (
      :cachedUserSubscribedMagazines0,
      :cachedUserSubscribedMagazines1,
      :cachedUserSubscribedMagazines2,
      :cachedUserSubscribedMagazines3,
      :cachedUserSubscribedMagazines4,
      :cachedUserSubscribedMagazines5,
      :cachedUserSubscribedMagazines6,
      :cachedUserSubscribedMagazines7,
      :cachedUserSubscribedMagazines8,
      :cachedUserSubscribedMagazines9,
      :cachedUserSubscribedMagazines10,
      :cachedUserSubscribedMagazines11,
      :cachedUserSubscribedMagazines12,
      :cachedUserSubscribedMagazines13,
      :cachedUserSubscribedMagazines14,
      :cachedUserSubscribedMagazines15,
      :cachedUserSubscribedMagazines16,
      :cachedUserSubscribedMagazines17,
      :cachedUserSubscribedMagazines18,
      :cachedUserSubscribedMagazines19,
      :cachedUserSubscribedMagazines20,
      :cachedUserSubscribedMagazines21,
      :cachedUserSubscribedMagazines22,
      :cachedUserSubscribedMagazines23,
      :cachedUserSubscribedMagazines24,
      :cachedUserSubscribedMagazines25,
      :cachedUserSubscribedMagazines26,
      :cachedUserSubscribedMagazines27,
      :cachedUserSubscribedMagazines28,
      :cachedUserSubscribedMagazines29,
      :cachedUserSubscribedMagazines30,
      :cachedUserSubscribedMagazines31,
      :cachedUserSubscribedMagazines32,
      :cachedUserSubscribedMagazines33,
      :cachedUserSubscribedMagazines34,
      :cachedUserSubscribedMagazines35,
      :cachedUserSubscribedMagazines36,
      :cachedUserSubscribedMagazines37,
      :cachedUserSubscribedMagazines38,
      :cachedUserSubscribedMagazines39,
      :cachedUserSubscribedMagazines40,
      :cachedUserSubscribedMagazines41,
      :cachedUserSubscribedMagazines42,
      :cachedUserSubscribedMagazines43,
      :cachedUserSubscribedMagazines44,
      :cachedUserSubscribedMagazines45,
      :cachedUserSubscribedMagazines46,
      :cachedUserSubscribedMagazines47,
      :cachedUserSubscribedMagazines48,
      :cachedUserSubscribedMagazines49,
      :cachedUserSubscribedMagazines50,
      :cachedUserSubscribedMagazines51,
      :cachedUserSubscribedMagazines52,
      :cachedUserSubscribedMagazines53,
      :cachedUserSubscribedMagazines54,
      :cachedUserSubscribedMagazines55,
      :cachedUserSubscribedMagazines56,
      :cachedUserSubscribedMagazines57,
      :cachedUserSubscribedMagazines58,
      :cachedUserSubscribedMagazines59,
      :cachedUserSubscribedMagazines60,
      :cachedUserSubscribedMagazines61,
      :cachedUserSubscribedMagazines62,
      :cachedUserSubscribedMagazines63,
      :cachedUserSubscribedMagazines64,
      :cachedUserSubscribedMagazines65,
      :cachedUserSubscribedMagazines66,
      :cachedUserSubscribedMagazines67,
      :cachedUserSubscribedMagazines68,
      :cachedUserSubscribedMagazines69,
      :cachedUserSubscribedMagazines70,
      :cachedUserSubscribedMagazines71,
      :cachedUserSubscribedMagazines72,
      :cachedUserSubscribedMagazines73,
      :cachedUserSubscribedMagazines74,
      :cachedUserSubscribedMagazines75,
      :cachedUserSubscribedMagazines76,
      :cachedUserSubscribedMagazines77,
      :cachedUserSubscribedMagazines78,
      :cachedUserSubscribedMagazines79,
      :cachedUserSubscribedMagazines80,
      :cachedUserSubscribedMagazines81,
      :cachedUserSubscribedMagazines82,
      :cachedUserSubscribedMagazines83,
      :cachedUserSubscribedMagazines84,
      :cachedUserSubscribedMagazines85,
      :cachedUserSubscribedMagazines86,
      :cachedUserSubscribedMagazines87,
      :cachedUserSubscribedMagazines88,
      :cachedUserSubscribedMagazines89,
      :cachedUserSubscribedMagazines90,
      :cachedUserSubscribedMagazines91,
      :cachedUserSubscribedMagazines92,
      :cachedUserSubscribedMagazines93,
      :cachedUserSubscribedMagazines94,
      :cachedUserSubscribedMagazines95,
      :cachedUserSubscribedMagazines96,
      :cachedUserSubscribedMagazines97,
      :cachedUserSubscribedMagazines98,
      :cachedUserSubscribedMagazines99,
      :cachedUserSubscribedMagazines100,
      :cachedUserSubscribedMagazines101,
      :cachedUserSubscribedMagazines102,
      :cachedUserSubscribedMagazines103,
      :cachedUserSubscribedMagazines104,
      :cachedUserSubscribedMagazines105,
      :cachedUserSubscribedMagazines106,
      :cachedUserSubscribedMagazines107,
      :cachedUserSubscribedMagazines108,
      :cachedUserSubscribedMagazines109,
      :cachedUserSubscribedMagazines110,
      :cachedUserSubscribedMagazines111,
      :cachedUserSubscribedMagazines112,
      :cachedUserSubscribedMagazines113,
      :cachedUserSubscribedMagazines114,
      :cachedUserSubscribedMagazines115,
      :cachedUserSubscribedMagazines116,
      :cachedUserSubscribedMagazines117,
      :cachedUserSubscribedMagazines118,
      :cachedUserSubscribedMagazines119,
      :cachedUserSubscribedMagazines120,
      :cachedUserSubscribedMagazines121,
      :cachedUserSubscribedMagazines122,
      :cachedUserSubscribedMagazines123,
      :cachedUserSubscribedMagazines124,
      :cachedUserSubscribedMagazines125,
      :cachedUserSubscribedMagazines126,
      :cachedUserSubscribedMagazines127,
      :cachedUserSubscribedMagazines128,
      :cachedUserSubscribedMagazines129,
      :cachedUserSubscribedMagazines130,
      :cachedUserSubscribedMagazines131,
      :cachedUserSubscribedMagazines132,
      :cachedUserSubscribedMagazines133,
      :cachedUserSubscribedMagazines134,
      :cachedUserSubscribedMagazines135,
      :cachedUserSubscribedMagazines136,
      :cachedUserSubscribedMagazines137,
      :cachedUserSubscribedMagazines138,
      :cachedUserSubscribedMagazines139,
      :cachedUserSubscribedMagazines140,
      :cachedUserSubscribedMagazines141,
      :cachedUserSubscribedMagazines142,
      :cachedUserSubscribedMagazines143,
      :cachedUserSubscribedMagazines144,
      :cachedUserSubscribedMagazines145,
      :cachedUserSubscribedMagazines146,
      :cachedUserSubscribedMagazines147,
      :cachedUserSubscribedMagazines148,
      :cachedUserSubscribedMagazines149,
      :cachedUserSubscribedMagazines150,
      :cachedUserSubscribedMagazines151,
      :cachedUserSubscribedMagazines152,
      :cachedUserSubscribedMagazines153,
      :cachedUserSubscribedMagazines154,
      :cachedUserSubscribedMagazines155,
      :cachedUserSubscribedMagazines156,
      :cachedUserSubscribedMagazines157,
      :cachedUserSubscribedMagazines158,
      :cachedUserSubscribedMagazines159,
      :cachedUserSubscribedMagazines160,
      :cachedUserSubscribedMagazines161,
      :cachedUserSubscribedMagazines162,
      :cachedUserSubscribedMagazines163,
      :cachedUserSubscribedMagazines164,
      :cachedUserSubscribedMagazines165,
      :cachedUserSubscribedMagazines166,
      :cachedUserSubscribedMagazines167,
      :cachedUserSubscribedMagazines168,
      :cachedUserSubscribedMagazines169,
      :cachedUserSubscribedMagazines170,
      :cachedUserSubscribedMagazines171,
      :cachedUserSubscribedMagazines172,
      :cachedUserSubscribedMagazines173,
      :cachedUserSubscribedMagazines174,
      :cachedUserSubscribedMagazines175,
      :cachedUserSubscribedMagazines176,
      :cachedUserSubscribedMagazines177,
      :cachedUserSubscribedMagazines178,
      :cachedUserSubscribedMagazines179,
      :cachedUserSubscribedMagazines180,
      :cachedUserSubscribedMagazines181,
      :cachedUserSubscribedMagazines182,
      :cachedUserSubscribedMagazines183,
      :cachedUserSubscribedMagazines184,
      :cachedUserSubscribedMagazines185,
      :cachedUserSubscribedMagazines186,
      :cachedUserSubscribedMagazines187,
      :cachedUserSubscribedMagazines188,
      :cachedUserSubscribedMagazines189,
      :cachedUserSubscribedMagazines190,
      :cachedUserSubscribedMagazines191,
      :cachedUserSubscribedMagazines192,
      :cachedUserSubscribedMagazines193,
      :cachedUserSubscribedMagazines194,
      :cachedUserSubscribedMagazines195,
      :cachedUserSubscribedMagazines196,
      :cachedUserSubscribedMagazines197,
      :cachedUserSubscribedMagazines198,
      :cachedUserSubscribedMagazines199,
      :cachedUserSubscribedMagazines200,
      :cachedUserSubscribedMagazines201,
      :cachedUserSubscribedMagazines202,
      :cachedUserSubscribedMagazines203,
      :cachedUserSubscribedMagazines204,
      :cachedUserSubscribedMagazines205,
      :cachedUserSubscribedMagazines206,
      :cachedUserSubscribedMagazines207,
      :cachedUserSubscribedMagazines208,
      :cachedUserSubscribedMagazines209,
      :cachedUserSubscribedMagazines210,
      :cachedUserSubscribedMagazines211,
      :cachedUserSubscribedMagazines212,
      :cachedUserSubscribedMagazines213,
      :cachedUserSubscribedMagazines214,
      :cachedUserSubscribedMagazines215,
      :cachedUserSubscribedMagazines216,
      :cachedUserSubscribedMagazines217,
      :cachedUserSubscribedMagazines218,
      :cachedUserSubscribedMagazines219,
      :cachedUserSubscribedMagazines220,
      :cachedUserSubscribedMagazines221,
      :cachedUserSubscribedMagazines222,
      :cachedUserSubscribedMagazines223,
      :cachedUserSubscribedMagazines224,
      :cachedUserSubscribedMagazines225,
      :cachedUserSubscribedMagazines226,
      :cachedUserSubscribedMagazines227,
      :cachedUserSubscribedMagazines228,
      :cachedUserSubscribedMagazines229,
      :cachedUserSubscribedMagazines230,
      :cachedUserSubscribedMagazines231,
      :cachedUserSubscribedMagazines232,
      :cachedUserSubscribedMagazines233,
      :cachedUserSubscribedMagazines234,
      :cachedUserSubscribedMagazines235,
      :cachedUserSubscribedMagazines236,
      :cachedUserSubscribedMagazines237,
      :cachedUserSubscribedMagazines238,
      :cachedUserSubscribedMagazines239,
      :cachedUserSubscribedMagazines240,
      :cachedUserSubscribedMagazines241,
      :cachedUserSubscribedMagazines242,
      :cachedUserSubscribedMagazines243,
      :cachedUserSubscribedMagazines244,
      :cachedUserSubscribedMagazines245,
      :cachedUserSubscribedMagazines246,
      :cachedUserSubscribedMagazines247,
      :cachedUserSubscribedMagazines248,
      :cachedUserSubscribedMagazines249,
      :cachedUserSubscribedMagazines250,
      :cachedUserSubscribedMagazines251,
      :cachedUserSubscribedMagazines252,
      :cachedUserSubscribedMagazines253,
      :cachedUserSubscribedMagazines254,
      :cachedUserSubscribedMagazines255,
      :cachedUserSubscribedMagazines256,
      :cachedUserSubscribedMagazines257,
      :cachedUserSubscribedMagazines258,
      :cachedUserSubscribedMagazines259,
      :cachedUserSubscribedMagazines260,
      :cachedUserSubscribedMagazines261,
      :cachedUserSubscribedMagazines262,
      :cachedUserSubscribedMagazines263,
      :cachedUserSubscribedMagazines264,
      :cachedUserSubscribedMagazines265,
      :cachedUserSubscribedMagazines266,
      :cachedUserSubscribedMagazines267,
      :cachedUserSubscribedMagazines268,
      :cachedUserSubscribedMagazines269,
      :cachedUserSubscribedMagazines270,
      :cachedUserSubscribedMagazines271,
      :cachedUserSubscribedMagazines272,
      :cachedUserSubscribedMagazines273,
      :cachedUserSubscribedMagazines274,
      :cachedUserSubscribedMagazines275,
      :cachedUserSubscribedMagazines276,
      :cachedUserSubscribedMagazines277,
      :cachedUserSubscribedMagazines278,
      :cachedUserSubscribedMagazines279,
      :cachedUserSubscribedMagazines280,
      :cachedUserSubscribedMagazines281,
      :cachedUserSubscribedMagazines282,
      :cachedUserSubscribedMagazines283,
      :cachedUserSubscribedMagazines284,
      :cachedUserSubscribedMagazines285,
      :cachedUserSubscribedMagazines286,
      :cachedUserSubscribedMagazines287,
      :cachedUserSubscribedMagazines288,
      :cachedUserSubscribedMagazines289,
      :cachedUserSubscribedMagazines290,
      :cachedUserSubscribedMagazines291,
      :cachedUserSubscribedMagazines292
    )
    OR u.id IN (
      :cachedUserFollows0, :cachedUserFollows1,
      :cachedUserFollows2, :cachedUserFollows3,
      :cachedUserFollows4, :cachedUserFollows5,
      :cachedUserFollows6, :cachedUserFollows7,
      :cachedUserFollows8, :cachedUserFollows9,
      :cachedUserFollows10, :cachedUserFollows11,
      :cachedUserFollows12, :cachedUserFollows13,
      :cachedUserFollows14, :cachedUserFollows15,
      :cachedUserFollows16, :cachedUserFollows17,
      :cachedUserFollows18, :cachedUserFollows19,
      :cachedUserFollows20, :cachedUserFollows21,
      :cachedUserFollows22, :cachedUserFollows23,
      :cachedUserFollows24, :cachedUserFollows25,
      :cachedUserFollows26, :cachedUserFollows27,
      :cachedUserFollows28, :cachedUserFollows29,
      :cachedUserFollows30, :cachedUserFollows31,
      :cachedUserFollows32, :cachedUserFollows33
    )
    OR d.id IN (:cachedUserSubscribedDomains)
  )
  AND (
    u.id NOT IN (
      :cachedUserBlocks0, :cachedUserBlocks1,
      :cachedUserBlocks2, :cachedUserBlocks3,
      :cachedUserBlocks4, :cachedUserBlocks5,
      :cachedUserBlocks6, :cachedUserBlocks7,
      :cachedUserBlocks8, :cachedUserBlocks9
    )
    AND (
      m IS NULL
      OR m.id NOT IN (
        :cachedUserBlockedMagazines0, :cachedUserBlockedMagazines1
      )
    )
    AND (
      d IS NULL
      OR d.id NOT IN (:cachedUserBlockedDomains)
    )
  )
  AND (m.visibility = :visible)
  AND (u.visibility = :visible)
  AND (
    c.visibility = :visible
    OR (
      c.visibility = :private
      AND u.id IN (
        :cachedUserFollows0, :cachedUserFollows1,
        :cachedUserFollows2, :cachedUserFollows3,
        :cachedUserFollows4, :cachedUserFollows5,
        :cachedUserFollows6, :cachedUserFollows7,
        :cachedUserFollows8, :cachedUserFollows9,
        :cachedUserFollows10, :cachedUserFollows11,
        :cachedUserFollows12, :cachedUserFollows13,
        :cachedUserFollows14, :cachedUserFollows15,
        :cachedUserFollows16, :cachedUserFollows17,
        :cachedUserFollows18, :cachedUserFollows19,
        :cachedUserFollows20, :cachedUserFollows21,
        :cachedUserFollows22, :cachedUserFollows23,
        :cachedUserFollows24, :cachedUserFollows25,
        :cachedUserFollows26, :cachedUserFollows27,
        :cachedUserFollows28, :cachedUserFollows29,
        :cachedUserFollows30, :cachedUserFollows31,
        :cachedUserFollows32, :cachedUserFollows33
      )
    )
  )
  AND (u.is_deleted = false)
UNION ALL
SELECT
  c.id,
  'post' as type,
  'microblog' as content_type,
  c.created_at,
  c.ranking,
  c.score,
  c.comment_count,
  c.sticky,
  c.last_active
FROM
  post c
  LEFT JOIN magazine m ON c.magazine_id = m.id
  INNER JOIN "user" u ON c.user_id = u.id
WHERE
  (
    c.user_id = :loggedInUser
    OR m.id IN (
      :cachedUserSubscribedMagazines0,
      :cachedUserSubscribedMagazines1,
      :cachedUserSubscribedMagazines2,
      :cachedUserSubscribedMagazines3,
      :cachedUserSubscribedMagazines4,
      :cachedUserSubscribedMagazines5,
      :cachedUserSubscribedMagazines6,
      :cachedUserSubscribedMagazines7,
      :cachedUserSubscribedMagazines8,
      :cachedUserSubscribedMagazines9,
      :cachedUserSubscribedMagazines10,
      :cachedUserSubscribedMagazines11,
      :cachedUserSubscribedMagazines12,
      :cachedUserSubscribedMagazines13,
      :cachedUserSubscribedMagazines14,
      :cachedUserSubscribedMagazines15,
      :cachedUserSubscribedMagazines16,
      :cachedUserSubscribedMagazines17,
      :cachedUserSubscribedMagazines18,
      :cachedUserSubscribedMagazines19,
      :cachedUserSubscribedMagazines20,
      :cachedUserSubscribedMagazines21,
      :cachedUserSubscribedMagazines22,
      :cachedUserSubscribedMagazines23,
      :cachedUserSubscribedMagazines24,
      :cachedUserSubscribedMagazines25,
      :cachedUserSubscribedMagazines26,
      :cachedUserSubscribedMagazines27,
      :cachedUserSubscribedMagazines28,
      :cachedUserSubscribedMagazines29,
      :cachedUserSubscribedMagazines30,
      :cachedUserSubscribedMagazines31,
      :cachedUserSubscribedMagazines32,
      :cachedUserSubscribedMagazines33,
      :cachedUserSubscribedMagazines34,
      :cachedUserSubscribedMagazines35,
      :cachedUserSubscribedMagazines36,
      :cachedUserSubscribedMagazines37,
      :cachedUserSubscribedMagazines38,
      :cachedUserSubscribedMagazines39,
      :cachedUserSubscribedMagazines40,
      :cachedUserSubscribedMagazines41,
      :cachedUserSubscribedMagazines42,
      :cachedUserSubscribedMagazines43,
      :cachedUserSubscribedMagazines44,
      :cachedUserSubscribedMagazines45,
      :cachedUserSubscribedMagazines46,
      :cachedUserSubscribedMagazines47,
      :cachedUserSubscribedMagazines48,
      :cachedUserSubscribedMagazines49,
      :cachedUserSubscribedMagazines50,
      :cachedUserSubscribedMagazines51,
      :cachedUserSubscribedMagazines52,
      :cachedUserSubscribedMagazines53,
      :cachedUserSubscribedMagazines54,
      :cachedUserSubscribedMagazines55,
      :cachedUserSubscribedMagazines56,
      :cachedUserSubscribedMagazines57,
      :cachedUserSubscribedMagazines58,
      :cachedUserSubscribedMagazines59,
      :cachedUserSubscribedMagazines60,
      :cachedUserSubscribedMagazines61,
      :cachedUserSubscribedMagazines62,
      :cachedUserSubscribedMagazines63,
      :cachedUserSubscribedMagazines64,
      :cachedUserSubscribedMagazines65,
      :cachedUserSubscribedMagazines66,
      :cachedUserSubscribedMagazines67,
      :cachedUserSubscribedMagazines68,
      :cachedUserSubscribedMagazines69,
      :cachedUserSubscribedMagazines70,
      :cachedUserSubscribedMagazines71,
      :cachedUserSubscribedMagazines72,
      :cachedUserSubscribedMagazines73,
      :cachedUserSubscribedMagazines74,
      :cachedUserSubscribedMagazines75,
      :cachedUserSubscribedMagazines76,
      :cachedUserSubscribedMagazines77,
      :cachedUserSubscribedMagazines78,
      :cachedUserSubscribedMagazines79,
      :cachedUserSubscribedMagazines80,
      :cachedUserSubscribedMagazines81,
      :cachedUserSubscribedMagazines82,
      :cachedUserSubscribedMagazines83,
      :cachedUserSubscribedMagazines84,
      :cachedUserSubscribedMagazines85,
      :cachedUserSubscribedMagazines86,
      :cachedUserSubscribedMagazines87,
      :cachedUserSubscribedMagazines88,
      :cachedUserSubscribedMagazines89,
      :cachedUserSubscribedMagazines90,
      :cachedUserSubscribedMagazines91,
      :cachedUserSubscribedMagazines92,
      :cachedUserSubscribedMagazines93,
      :cachedUserSubscribedMagazines94,
      :cachedUserSubscribedMagazines95,
      :cachedUserSubscribedMagazines96,
      :cachedUserSubscribedMagazines97,
      :cachedUserSubscribedMagazines98,
      :cachedUserSubscribedMagazines99,
      :cachedUserSubscribedMagazines100,
      :cachedUserSubscribedMagazines101,
      :cachedUserSubscribedMagazines102,
      :cachedUserSubscribedMagazines103,
      :cachedUserSubscribedMagazines104,
      :cachedUserSubscribedMagazines105,
      :cachedUserSubscribedMagazines106,
      :cachedUserSubscribedMagazines107,
      :cachedUserSubscribedMagazines108,
      :cachedUserSubscribedMagazines109,
      :cachedUserSubscribedMagazines110,
      :cachedUserSubscribedMagazines111,
      :cachedUserSubscribedMagazines112,
      :cachedUserSubscribedMagazines113,
      :cachedUserSubscribedMagazines114,
      :cachedUserSubscribedMagazines115,
      :cachedUserSubscribedMagazines116,
      :cachedUserSubscribedMagazines117,
      :cachedUserSubscribedMagazines118,
      :cachedUserSubscribedMagazines119,
      :cachedUserSubscribedMagazines120,
      :cachedUserSubscribedMagazines121,
      :cachedUserSubscribedMagazines122,
      :cachedUserSubscribedMagazines123,
      :cachedUserSubscribedMagazines124,
      :cachedUserSubscribedMagazines125,
      :cachedUserSubscribedMagazines126,
      :cachedUserSubscribedMagazines127,
      :cachedUserSubscribedMagazines128,
      :cachedUserSubscribedMagazines129,
      :cachedUserSubscribedMagazines130,
      :cachedUserSubscribedMagazines131,
      :cachedUserSubscribedMagazines132,
      :cachedUserSubscribedMagazines133,
      :cachedUserSubscribedMagazines134,
      :cachedUserSubscribedMagazines135,
      :cachedUserSubscribedMagazines136,
      :cachedUserSubscribedMagazines137,
      :cachedUserSubscribedMagazines138,
      :cachedUserSubscribedMagazines139,
      :cachedUserSubscribedMagazines140,
      :cachedUserSubscribedMagazines141,
      :cachedUserSubscribedMagazines142,
      :cachedUserSubscribedMagazines143,
      :cachedUserSubscribedMagazines144,
      :cachedUserSubscribedMagazines145,
      :cachedUserSubscribedMagazines146,
      :cachedUserSubscribedMagazines147,
      :cachedUserSubscribedMagazines148,
      :cachedUserSubscribedMagazines149,
      :cachedUserSubscribedMagazines150,
      :cachedUserSubscribedMagazines151,
      :cachedUserSubscribedMagazines152,
      :cachedUserSubscribedMagazines153,
      :cachedUserSubscribedMagazines154,
      :cachedUserSubscribedMagazines155,
      :cachedUserSubscribedMagazines156,
      :cachedUserSubscribedMagazines157,
      :cachedUserSubscribedMagazines158,
      :cachedUserSubscribedMagazines159,
      :cachedUserSubscribedMagazines160,
      :cachedUserSubscribedMagazines161,
      :cachedUserSubscribedMagazines162,
      :cachedUserSubscribedMagazines163,
      :cachedUserSubscribedMagazines164,
      :cachedUserSubscribedMagazines165,
      :cachedUserSubscribedMagazines166,
      :cachedUserSubscribedMagazines167,
      :cachedUserSubscribedMagazines168,
      :cachedUserSubscribedMagazines169,
      :cachedUserSubscribedMagazines170,
      :cachedUserSubscribedMagazines171,
      :cachedUserSubscribedMagazines172,
      :cachedUserSubscribedMagazines173,
      :cachedUserSubscribedMagazines174,
      :cachedUserSubscribedMagazines175,
      :cachedUserSubscribedMagazines176,
      :cachedUserSubscribedMagazines177,
      :cachedUserSubscribedMagazines178,
      :cachedUserSubscribedMagazines179,
      :cachedUserSubscribedMagazines180,
      :cachedUserSubscribedMagazines181,
      :cachedUserSubscribedMagazines182,
      :cachedUserSubscribedMagazines183,
      :cachedUserSubscribedMagazines184,
      :cachedUserSubscribedMagazines185,
      :cachedUserSubscribedMagazines186,
      :cachedUserSubscribedMagazines187,
      :cachedUserSubscribedMagazines188,
      :cachedUserSubscribedMagazines189,
      :cachedUserSubscribedMagazines190,
      :cachedUserSubscribedMagazines191,
      :cachedUserSubscribedMagazines192,
      :cachedUserSubscribedMagazines193,
      :cachedUserSubscribedMagazines194,
      :cachedUserSubscribedMagazines195,
      :cachedUserSubscribedMagazines196,
      :cachedUserSubscribedMagazines197,
      :cachedUserSubscribedMagazines198,
      :cachedUserSubscribedMagazines199,
      :cachedUserSubscribedMagazines200,
      :cachedUserSubscribedMagazines201,
      :cachedUserSubscribedMagazines202,
      :cachedUserSubscribedMagazines203,
      :cachedUserSubscribedMagazines204,
      :cachedUserSubscribedMagazines205,
      :cachedUserSubscribedMagazines206,
      :cachedUserSubscribedMagazines207,
      :cachedUserSubscribedMagazines208,
      :cachedUserSubscribedMagazines209,
      :cachedUserSubscribedMagazines210,
      :cachedUserSubscribedMagazines211,
      :cachedUserSubscribedMagazines212,
      :cachedUserSubscribedMagazines213,
      :cachedUserSubscribedMagazines214,
      :cachedUserSubscribedMagazines215,
      :cachedUserSubscribedMagazines216,
      :cachedUserSubscribedMagazines217,
      :cachedUserSubscribedMagazines218,
      :cachedUserSubscribedMagazines219,
      :cachedUserSubscribedMagazines220,
      :cachedUserSubscribedMagazines221,
      :cachedUserSubscribedMagazines222,
      :cachedUserSubscribedMagazines223,
      :cachedUserSubscribedMagazines224,
      :cachedUserSubscribedMagazines225,
      :cachedUserSubscribedMagazines226,
      :cachedUserSubscribedMagazines227,
      :cachedUserSubscribedMagazines228,
      :cachedUserSubscribedMagazines229,
      :cachedUserSubscribedMagazines230,
      :cachedUserSubscribedMagazines231,
      :cachedUserSubscribedMagazines232,
      :cachedUserSubscribedMagazines233,
      :cachedUserSubscribedMagazines234,
      :cachedUserSubscribedMagazines235,
      :cachedUserSubscribedMagazines236,
      :cachedUserSubscribedMagazines237,
      :cachedUserSubscribedMagazines238,
      :cachedUserSubscribedMagazines239,
      :cachedUserSubscribedMagazines240,
      :cachedUserSubscribedMagazines241,
      :cachedUserSubscribedMagazines242,
      :cachedUserSubscribedMagazines243,
      :cachedUserSubscribedMagazines244,
      :cachedUserSubscribedMagazines245,
      :cachedUserSubscribedMagazines246,
      :cachedUserSubscribedMagazines247,
      :cachedUserSubscribedMagazines248,
      :cachedUserSubscribedMagazines249,
      :cachedUserSubscribedMagazines250,
      :cachedUserSubscribedMagazines251,
      :cachedUserSubscribedMagazines252,
      :cachedUserSubscribedMagazines253,
      :cachedUserSubscribedMagazines254,
      :cachedUserSubscribedMagazines255,
      :cachedUserSubscribedMagazines256,
      :cachedUserSubscribedMagazines257,
      :cachedUserSubscribedMagazines258,
      :cachedUserSubscribedMagazines259,
      :cachedUserSubscribedMagazines260,
      :cachedUserSubscribedMagazines261,
      :cachedUserSubscribedMagazines262,
      :cachedUserSubscribedMagazines263,
      :cachedUserSubscribedMagazines264,
      :cachedUserSubscribedMagazines265,
      :cachedUserSubscribedMagazines266,
      :cachedUserSubscribedMagazines267,
      :cachedUserSubscribedMagazines268,
      :cachedUserSubscribedMagazines269,
      :cachedUserSubscribedMagazines270,
      :cachedUserSubscribedMagazines271,
      :cachedUserSubscribedMagazines272,
      :cachedUserSubscribedMagazines273,
      :cachedUserSubscribedMagazines274,
      :cachedUserSubscribedMagazines275,
      :cachedUserSubscribedMagazines276,
      :cachedUserSubscribedMagazines277,
      :cachedUserSubscribedMagazines278,
      :cachedUserSubscribedMagazines279,
      :cachedUserSubscribedMagazines280,
      :cachedUserSubscribedMagazines281,
      :cachedUserSubscribedMagazines282,
      :cachedUserSubscribedMagazines283,
      :cachedUserSubscribedMagazines284,
      :cachedUserSubscribedMagazines285,
      :cachedUserSubscribedMagazines286,
      :cachedUserSubscribedMagazines287,
      :cachedUserSubscribedMagazines288,
      :cachedUserSubscribedMagazines289,
      :cachedUserSubscribedMagazines290,
      :cachedUserSubscribedMagazines291,
      :cachedUserSubscribedMagazines292
    )
    OR u.id IN (
      :cachedUserFollows0, :cachedUserFollows1,
      :cachedUserFollows2, :cachedUserFollows3,
      :cachedUserFollows4, :cachedUserFollows5,
      :cachedUserFollows6, :cachedUserFollows7,
      :cachedUserFollows8, :cachedUserFollows9,
      :cachedUserFollows10, :cachedUserFollows11,
      :cachedUserFollows12, :cachedUserFollows13,
      :cachedUserFollows14, :cachedUserFollows15,
      :cachedUserFollows16, :cachedUserFollows17,
      :cachedUserFollows18, :cachedUserFollows19,
      :cachedUserFollows20, :cachedUserFollows21,
      :cachedUserFollows22, :cachedUserFollows23,
      :cachedUserFollows24, :cachedUserFollows25,
      :cachedUserFollows26, :cachedUserFollows27,
      :cachedUserFollows28, :cachedUserFollows29,
      :cachedUserFollows30, :cachedUserFollows31,
      :cachedUserFollows32, :cachedUserFollows33
    )
  )
  AND (
    u.id NOT IN (
      :cachedUserBlocks0, :cachedUserBlocks1,
      :cachedUserBlocks2, :cachedUserBlocks3,
      :cachedUserBlocks4, :cachedUserBlocks5,
      :cachedUserBlocks6, :cachedUserBlocks7,
      :cachedUserBlocks8, :cachedUserBlocks9
    )
    AND (
      m IS NULL
      OR m.id NOT IN (
        :cachedUserBlockedMagazines0, :cachedUserBlockedMagazines1
      )
    )
  )
  AND (m.visibility = :visible)
  AND (u.visibility = :visible)
  AND (
    c.visibility = :visible
    OR (
      c.visibility = :private
      AND u.id IN (
        :cachedUserFollows0, :cachedUserFollows1,
        :cachedUserFollows2, :cachedUserFollows3,
        :cachedUserFollows4, :cachedUserFollows5,
        :cachedUserFollows6, :cachedUserFollows7,
        :cachedUserFollows8, :cachedUserFollows9,
        :cachedUserFollows10, :cachedUserFollows11,
        :cachedUserFollows12, :cachedUserFollows13,
        :cachedUserFollows14, :cachedUserFollows15,
        :cachedUserFollows16, :cachedUserFollows17,
        :cachedUserFollows18, :cachedUserFollows19,
        :cachedUserFollows20, :cachedUserFollows21,
        :cachedUserFollows22, :cachedUserFollows23,
        :cachedUserFollows24, :cachedUserFollows25,
        :cachedUserFollows26, :cachedUserFollows27,
        :cachedUserFollows28, :cachedUserFollows29,
        :cachedUserFollows30, :cachedUserFollows31,
        :cachedUserFollows32, :cachedUserFollows33
      )
    )
  )
  AND (u.is_deleted = false)
ORDER BY
  ranking DESC,
  created_at DESC
LIMIT
  :limit
OFFSET
  :offset

So lets analyze this one here you go: https://explain.dalibo.com/plan/h252dhc61872h37e

On which Mbin instance did you find the bug?

kbin.melroy.org

Which Mbin version was running on the instance?

1.9.0-rc1 (all prio releases are also affected)

To Reproduce
Steps to reproduce the behavior:

  1. Set to dev mode
  2. Login
  3. Clear caches
  4. Go to any page
  5. See profile and order by duration of Doctrine queries.

Expected behavior

SQL queries should take max. 5ms, at most ~10ms.. This query is taking between 450 and 550 ms (on a very high-end server).

This is insane.

Screenshots

Image Image

One of the query that is standing out is for example the parallel hash join, this goes over 801,051 records / rows in the DB:

Image

But this is just one example. I believe this query can be improved so much on so many levels.

Additional context

If we can fix this one.. We really want to take a look at post (microblog) one as well, since that one is very similar as well:

https://github.com/MbinOrg/mbin/blob/main/src/Repository/PostRepository.php#L378 (same nesting issues and same reverse order by search etc.)

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingenhancementNew feature or requestperformanceThis is a issue regarding performance.

    Type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions