XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.26, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • Fix Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      CentOS 7

      Description

      After upgrade on 10.4.26 after changing joined tables in WHERE clause the plan getting worse:

      SELECT
         subscriptionId, string, stringId, userId, webId, cron, lastAutoId
        FROM subscriptions.send se
        JOIN subscriptions.subscriptions  s USING(subscriptionId)
        JOIN subscriptions.profiles USING(profileId)
        JOIN subscriptions.strings USING(stringId)
        JOIN subscriptions.schedule USING(scheduleId)
        WHERE next <= NOW()
          AND se.subscriptionId IN (13304884, 4996516, 422541, 6809130, 7508523, 11254813, 11428600, 13272640, 83632, 13211679, 10155318, 10445013, 9405478, 9031280, 10738519, 13090088, 12006096, 11774593, 5770179, 9977691, 5053234, 13024588, 6246532, 9801612, 8930459, 9484062, 9951533, 3284553, 10883216, 4360798, 8654256, 3070161, 13262056, 8277648, 3673463, 11344523, 7924664, 9705242, 11522204, 8594145, 5431666, 11872542, 12514647, 11898128, 10435159, 8988977, 11392579, 13212233, 11352298, 11925584, 9763763, 10474257, 3057850, 12743391, 12195993, 8842699, 7919760, 729582, 12687723, 7942806, 8956429, 13221336, 13203976, 11212117, 12622147, 4091809, 11601268, 11958971, 11732948, 9935172, 11857650, 11550494, 13363744, 6282972, 10791765, 11669239, 8100300, 6231592, 4376705, 12076875, 9369980, 4280110, 13277244, 10747976, 11563803, 8666015, 12129569, 225808, 10886926, 10599569, 6357250, 9192050, 952478, 11696656, 11013805, 10808603, 8952390, 11796269, 2536646)
          AND platform != 1
      

      has the plan below:

      1,SIMPLE,profiles,ALL,PRIMARY,NULL,NULL,NULL,2930162,100,
      1,SIMPLE,subscriptions,ref,"PRIMARY,profiles_ibfk_sub,schedule_ibfk_sub,strings_ibfk_sub",profiles_ibfk_sub,4,subscriptions.profiles.profileId,1,100,Using where
      1,SIMPLE,schedule,eq_ref,PRIMARY,PRIMARY,4,subscriptions.subscriptions.scheduleId,1,100,
      1,SIMPLE,send,eq_ref,"PRIMARY,next_idx",PRIMARY,4,subscriptions.subscriptions.subscriptionId,1,100,Using where
      1,SIMPLE,strings,eq_ref,PRIMARY,PRIMARY,8,subscriptions.subscriptions.stringId,1,100
      

      The proper plan:

      SELECT
         subscriptionId, string, stringId, userId, webId, cron, lastAutoId
        FROM subscriptions.send se
        JOIN subscriptions.subscriptions s USING(subscriptionId)
        JOIN subscriptions.profiles USING(profileId)
        JOIN subscriptions.strings USING(stringId)
        JOIN subscriptions.schedule USING(scheduleId)
        WHERE next <= NOW()
          AND s.subscriptionId IN (13304884, 4996516, 422541, 6809130, 7508523, 11254813, 11428600, 13272640, 83632, 13211679, 10155318, 10445013, 9405478, 9031280, 10738519, 13090088, 12006096, 11774593, 5770179, 9977691, 5053234, 13024588, 6246532, 9801612, 8930459, 9484062, 9951533, 3284553, 10883216, 4360798, 8654256, 3070161, 13262056, 8277648, 3673463, 11344523, 7924664, 9705242, 11522204, 8594145, 5431666, 11872542, 12514647, 11898128, 10435159, 8988977, 11392579, 13212233, 11352298, 11925584, 9763763, 10474257, 3057850, 12743391, 12195993, 8842699, 7919760, 729582, 12687723, 7942806, 8956429, 13221336, 13203976, 11212117, 12622147, 4091809, 11601268, 11958971, 11732948, 9935172, 11857650, 11550494, 13363744, 6282972, 10791765, 11669239, 8100300, 6231592, 4376705, 12076875, 9369980, 4280110, 13277244, 10747976, 11563803, 8666015, 12129569, 225808, 10886926, 10599569, 6357250, 9192050, 952478, 11696656, 11013805, 10808603, 8952390, 11796269, 2536646)
          AND platform != 1
      

      1,SIMPLE,s,range,"PRIMARY,profiles_ibfk_sub,schedule_ibfk_sub,strings_ibfk_sub",PRIMARY,4,NULL,99,100,Using where
      1,SIMPLE,schedule,eq_ref,PRIMARY,PRIMARY,4,subscriptions.s.scheduleId,1,100,
      1,SIMPLE,send,eq_ref,"PRIMARY,next_idx",PRIMARY,4,subscriptions.s.subscriptionId,1,50,Using where
      1,SIMPLE,profiles,eq_ref,PRIMARY,PRIMARY,4,subscriptions.s.profileId,1,100,
      1,SIMPLE,strings,eq_ref,PRIMARY,PRIMARY,8,subscriptions.s.stringId,1,100,
      

      On previous version 10.3.28 it works with proper plan regardless the table given in WHERE clause

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergei Sergei Petrunia
              Reporter:
              Moroz Vova Moroz
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.