Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-29571

MFR_MESSAGE_DELETED_I causes bad performance

    Details

    • Type: Bug
    • Status: CLOSED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4
    • Fix Version/s: 10.6
    • Component/s: Forums Tool
    • Labels:
      None
    • 10 status:
      Resolved

      Description

      Recently it was seen that MySQL was selecting a bad query plan that involved the use of the following index:
      MFR_MESSAGE_DELETED_I

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE areaimpl3_ ref PRIMARY,CONTEXT_ID,MFR_AREA_CONTEXT_I MFR_AREA_CONTEXT_I 767 cons 2 Using where; Using index
      1 SIMPLE openforumi2_ ref PRIMARY,FKC17608478B5E2A2F,MFR_OF_PAR_BASEFRM FKC17608478B5E2A2F 9 pc.areaimpl3_.ID 1 Using index
      1 SIMPLE messageimp0_ ref MFR_MESSAGE_DELETED_I MFR_MESSAGE_DELETED_I 1 const 104886 Using where
      1 SIMPLE topicimpl1_ eq_ref PRIMARY,FK863DC0BE74C7E92B,MFR_TOPIC_PAR_I1 PRIMARY 8 pc.messageimp0_.surrogateKey 1 Using where

      this query took more than 6 seconds to run

      select count(*) as col_0_0_ 
          from MFR_MESSAGE_T messageimp0_ 
              inner join MFR_TOPIC_T topicimpl1_ on messageimp0_.surrogateKey=topicimpl1_.ID 
              inner join MFR_OPEN_FORUM_T openforumi2_ on topicimpl1_.of_surrogateKey=openforumi2_.ID 
              inner join MFR_AREA_T areaimpl3_ on openforumi2_.surrogateKey=areaimpl3_.ID 
          where areaimpl3_.CONTEXT_ID='525e4dfd-b3e3-4b35-8988-0de3645330f5'
              and messageimp0_.DRAFT=0 
              and messageimp0_.DELETED=0 
              and messageimp0_.CREATED_BY='1dfa1ae4-d1c1-4c5b-bc26-e6a74b6598a2';
      

      The cardinality on a bit(1) field is horrible and should never be used

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  ern Earle R Nietzel
                  Reporter:
                  ern Earle R Nietzel
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  6 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration