Uploaded image for project: 'XWiki Platform'
  1. XWiki Platform
  2. XWIKI-13700

The document tree is still slow on MySql

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 8.3-milestone-2
    • Fix Version/s: 8.3-rc-1
    • Component/s: Index
    • Labels:
      None
    • Tests:
      Integration
    • Development Priority:
      High
    • Difficulty:
      Hard
    • Documentation:
      N/A
    • Documentation in Release Notes:
      N/A
    • Similar issues:

      Description

      After fixing XWIKI-13468 we noticed that the document tree was very slow on MySql when expanding a tree node with 5k child nodes. This is the SQL query that is executed on the database:

      select xwikipage0_.reference as col_0_0, xwikipage0_.terminal as col_1_0
      
          from (
              select XWS_REFERENCE as reference, false as terminal, XWS_NAME as name, XWS_PARENT as parent, XWS_HIDDEN as hidden
                  from xwikispace
              union all
              select XWD_FULLNAME as reference, true as terminal, XWD_NAME as name, XWD_WEB as parent, XWD_HIDDEN as hidden
                  from xwikidoc
                  where XWD_TRANSLATION = 0 and XWD_NAME <> 'WebHome'
          ) xwikipage0_ left outer join (
              select case when XWD_NAME = 'WebHome' then XWD_WEB else XWD_FULLNAME end as reference,
                     case when XWD_NAME = 'WebHome' then false else true end as terminal,
                     nullif(XWD_TITLE, '') as title, XWD_LANGUAGE as locale, XWD_DEFAULT_LANGUAGE as defaultLocale
                  from xwikidoc
          ) translatio1_ on xwikipage0_.reference=translatio1_.reference and xwikipage0_.terminal=translatio1_.terminal and (translatio1_.locale='') left outer join (
              select case when XWD_NAME = 'WebHome' then XWD_WEB else XWD_FULLNAME end as reference,
                     case when XWD_NAME = 'WebHome' then false else true end as terminal,
                     nullif(XWD_TITLE, '') as title, XWD_LANGUAGE as locale, XWD_DEFAULT_LANGUAGE as defaultLocale
                  from xwikidoc
          ) translatio2_ on xwikipage0_.reference=translatio2_.reference and xwikipage0_.terminal=translatio2_.terminal and (translatio2_.locale='en')
      
          where xwikipage0_.parent='Test' and xwikipage0_.hidden<>1
      
          order by lower(coalesce(translatio2_.title, translatio1_.title, xwikipage0_.name)),
                   coalesce(translatio2_.title, translatio1_.title, xwikipage0_.name)
      
          limit 15
      

      Initially we thought the query is slow only because of the complex order by clause but it turns out that it is slow also when performing pagination without sorting. The first page is loaded very fast (200ms without sorting) but the query becomes slower and slower as we increase the offset (3s on the 5th page). The query takes at least 32s when sorting by title.

      The slowness seems to be caused by the fact that we join and apply constraints to a temporary table which is the result of a union. Temporary tables don't have indexes and thus retrieving the rows is very slow (compared to a real table). The recommended solution is to move the joins and constrains inside the union, even if this means duplicating them for each of the unified selects.

      What's strange is that HSQLDB, Oracle and PostgreSql don't have this problem. It seems that MySql's query optimizer is not as good as theirs (it doesn't know how to re-write the query so that the joins and constrains are inside the union).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                mflorea Marius Dumitru Florea
                Reporter:
                mflorea Marius Dumitru Florea
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Date of First Response: