Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2381

Pagination query can be simplified when simple joins are applied

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.3, 2.4
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • Labels:

      Description

      Hi.
      In mysql db table i have > 200,000 items.
      I use native doctrine pagination for paging the items list.
      But generated query that gets ids for items list in paging works more then 150 sec on my workstation

      SELECT DISTINCT id0 FROM (SELECT m0_.id AS id0, m0_.title AS title1, m0_.text AS text2, m0_.price AS price3, m0_.originalPrice AS originalPrice4, m0_.condition_type AS condition_type5, m0_.image_1 AS image_16, m0_.image_2 AS image_27, m0_.image_3 AS image_38, m0_.image_4 AS image_49, m0_.image_5 AS image_510, m0_.video AS video11, m0_.contact_email AS contact_email12, m0_.contact_name AS contact_name13, m0_.contact_phone AS contact_phone14, m0_.contact_type AS contact_type15, m0_.published AS published16, m0_.type AS type17, m0_.status AS status18, m0_.highlight AS highlight19, m0_.urgent AS urgent20, m0_.topads AS topads21, m0_.period AS period22, m0_.hits AS hits23, m0_.ip AS ip24, m0_.created_at AS created_at25, m0_.updated_at AS updated_at26 FROM milla_message m0_ INNER JOIN milla_currency m1_ ON m0_.currency_id = m1_.id INNER JOIN milla_category m2_ ON m0_.category_id = m2_.id INNER JOIN milla_region m3_ ON m0_.region_id = m3_.id INNER JOIN milla_city m4_ ON m0_.city_id = m4_.id WHERE m0_.status = 1 ORDER BY m0_.published DESC) dctrn_result LIMIT 20 OFFSET 0

      source code
      https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L141

      why SELECT DISTINCT %s FROM (%s) dctrn_result ???
      why not SELECT DISTINCT m0_.id AS id0 FROM milla_message m0_ WHERE m0_.status = 1 ORDER BY m0_.published DESC LIMIT 20 OFFSET 0

      1. EXPLAIN.htmL
        7 kB
        Sergey Gerdel
      2. EXPLAIN1.html
        3 kB
        Sergey Gerdel
      3. EXPLAIN2.html
        5 kB
        Sergey Gerdel
      4. EXPLAIN3.htm
        4 kB
        Sergey Gerdel
      5. EXPLAIN4.htm
        3 kB
        Sergey Gerdel

        Activity

        Sergey Gerdel created issue -
        Sergey Gerdel made changes -
        Field Original Value New Value
        Description Hi.
        In mysql db table i have > 200,000 items.
        I use native doctrine pagination for paging the items list.
        But generated query that gets ids for items list in paging works more then 150 sec on my workstation

        SELECT DISTINCT id0 FROM (SELECT m0_.id AS id0, m0_.title AS title1, m0_.text AS text2, m0_.price AS price3, m0_.originalPrice AS originalPrice4, m0_.condition_type AS condition_type5, m0_.image_1 AS image_16, m0_.image_2 AS image_27, m0_.image_3 AS image_38, m0_.image_4 AS image_49, m0_.image_5 AS image_510, m0_.video AS video11, m0_.contact_email AS contact_email12, m0_.contact_name AS contact_name13, m0_.contact_phone AS contact_phone14, m0_.contact_type AS contact_type15, m0_.published AS published16, m0_.type AS type17, m0_.status AS status18, m0_.highlight AS highlight19, m0_.urgent AS urgent20, m0_.topads AS topads21, m0_.period AS period22, m0_.hits AS hits23, m0_.ip AS ip24, m0_.created_at AS created_at25, m0_.updated_at AS updated_at26 FROM milla_message m0_ INNER JOIN milla_currency m1_ ON m0_.currency_id = m1_.id INNER JOIN milla_category m2_ ON m0_.category_id = m2_.id INNER JOIN milla_region m3_ ON m0_.region_id = m3_.id INNER JOIN milla_city m4_ ON m0_.city_id = m4_.id WHERE m0_.status = 1 ORDER BY m0_.published DESC) dctrn_result LIMIT 20 OFFSET 0

        source code
        https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L141

        why SELECT DISTINCT %s FROM (%s) dctrn_result ???
        why not SELECT DISTINCT m0_.id AS id0, m0_.id AS id1 FROM milla_message m0_ WHERE m0_.status = 1 ORDER BY m0_.published DESC LIMIT 20 OFFSET 0
        Hi.
        In mysql db table i have > 200,000 items.
        I use native doctrine pagination for paging the items list.
        But generated query that gets ids for items list in paging works more then 150 sec on my workstation

        SELECT DISTINCT id0 FROM (SELECT m0_.id AS id0, m0_.title AS title1, m0_.text AS text2, m0_.price AS price3, m0_.originalPrice AS originalPrice4, m0_.condition_type AS condition_type5, m0_.image_1 AS image_16, m0_.image_2 AS image_27, m0_.image_3 AS image_38, m0_.image_4 AS image_49, m0_.image_5 AS image_510, m0_.video AS video11, m0_.contact_email AS contact_email12, m0_.contact_name AS contact_name13, m0_.contact_phone AS contact_phone14, m0_.contact_type AS contact_type15, m0_.published AS published16, m0_.type AS type17, m0_.status AS status18, m0_.highlight AS highlight19, m0_.urgent AS urgent20, m0_.topads AS topads21, m0_.period AS period22, m0_.hits AS hits23, m0_.ip AS ip24, m0_.created_at AS created_at25, m0_.updated_at AS updated_at26 FROM milla_message m0_ INNER JOIN milla_currency m1_ ON m0_.currency_id = m1_.id INNER JOIN milla_category m2_ ON m0_.category_id = m2_.id INNER JOIN milla_region m3_ ON m0_.region_id = m3_.id INNER JOIN milla_city m4_ ON m0_.city_id = m4_.id WHERE m0_.status = 1 ORDER BY m0_.published DESC) dctrn_result LIMIT 20 OFFSET 0

        source code
        https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Tools/Pagination/LimitSubqueryOutputWalker.php#L141

        why SELECT DISTINCT %s FROM (%s) dctrn_result ???
        why not SELECT DISTINCT m0_.id AS id0 FROM milla_message m0_ WHERE m0_.status = 1 ORDER BY m0_.published DESC LIMIT 20 OFFSET 0
        Marco Pivetta made changes -
        Assignee Benjamin Eberlei [ beberlei ] Marco Pivetta [ ocramius ]
        Priority Blocker [ 1 ] Major [ 3 ]
        Sergey Gerdel made changes -
        Attachment EXPLAIN1.html [ 11515 ]
        Sergey Gerdel made changes -
        Attachment EXPLAIN2.html [ 11516 ]
        Sergey Gerdel made changes -
        Attachment EXPLAIN2.html [ 11517 ]
        Sergey Gerdel made changes -
        Attachment EXPLAIN2.html [ 11516 ]
        Sergey Gerdel made changes -
        Attachment EXPLAIN3.htm [ 11518 ]
        Attachment EXPLAIN4.htm [ 11519 ]
        Marco Pivetta made changes -
        Summary Pagination query goes work for 150sec Pagination query can be simplified when simple joins are applied
        Issue Type Bug [ 1 ] Improvement [ 4 ]
        Priority Major [ 3 ] Minor [ 4 ]

          People

          • Assignee:
            Marco Pivetta
            Reporter:
            Sergey Gerdel
          • Votes:
            2 Vote for this issue
            Watchers:
            7 Start watching this issue

            Dates

            • Created:
              Updated: