XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.26, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
    • 10.4, 10.5, 10.6, 10.11
    • Optimizer
    • 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

              psergei Sergei Petrunia
              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.