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

rest api query called by XE JumpToPage causes performance problem and even deadlock

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.3.1, 2.4 M1
    • Fix Version/s: 2.5 RC1, 2.4.4
    • Component/s: REST
    • Labels:
      None
    • Tests:
      Integration
    • Difficulty:
      Unknown
    • Similar issues:

      Description

      The following rest api call (which is used by the JumpToPage feature in XE)

      https://rpq.emc.devxwiki.com/xwiki/rest/wikis/xwiki/search?scope=name&number=10&media=json&q=a

      is generating the following mysql query

      select distinct xwikidocum0_.XWD_WEB as col_0_0_, xwikidocum0_.XWD_NAME as col_1_0_, xwikidocum0_.XWD_LANGUAGE as col_2_0_ from xwikidoc xwikidocum0_ where upper(xwikidocum0_.XWD_FULLNAME) like '%a%' order by xwikidocum0_.XWD_WEB, xwikidocum0_.XWD_NAME asc limit 10

      unfortunately this query is very suboptimal and can bring down a wiki with many documents (which have an 'a' in them), because mysql cannot use indexes with 2 keys in the "order by" clause. See http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

      "In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following. You use ORDER BY on different keys: SELECT * FROM t1 ORDER BY key1, key2;"

      Now this query can be very easily changed by

      select distinct xwikidocum0_.XWD_WEB as col_0_0_, xwikidocum0_.XWD_NAME as col_1_0_, xwikidocum0_.XWD_LANGUAGE as col_2_0_ from xwikidoc xwikidocum0_ where upper(xwikidocum0_.XWD_FULLNAME) like '%a%' order by xwikidocum0_.XWD_FULLNAME asc limit 10

      and it becomes very fast !

      The code in the REST api is in

       if (hasProgrammingRights)

      {                f.format(") order by doc.space, doc.name asc"); and  }

      else

      {                f.format(") and doc.space<>'XWiki' and doc.space<>'Admin' and doc.space<>'Panels' and doc.name<>'WebPreferences' order by doc.space, doc.name asc");            }

      it should be replaced with "doc.fullName" instead of doc.space, doc.name

        Attachments

          Activity

            People

            Assignee:
            fmancinelli Fabio Mancinelli
            Reporter:
            ludovic Ludovic Dubost
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:
              Date of First Response: