[Building Sakai] MFR_MESSAGE_T.SURROGATEKEY field with null values in active messages

[Will Humphries]

Hi Daniel,

I think mfr_message_t.surrogatekey might be null for private messages, 
which aren't in a forum topic. All of the mfr_message_t records in our 
db have a label that indicates they're a private message:

SQL> select label, count(*) from mfr_message_t where surrogatekey is 
null group by label;
pvt_priority_high
       5862

pvt_priority_normal
      87858

pvt_priority_low
     26


SQL> select label, count(*) from mfr_message_t where surrogatekey is not 
null group by label;

     155055

-Will

On 11/7/14 7:15 AM, Daniel Merino wrote:
> Hi Bryan,
>
> I think I have discovered one possible reason of our lack of performance
> in Forums / Private Messages.
>
> As I understand, the SURROGATEKEY field always points to the level above
> it. So MFR_MESSAGE_T.SURROGATEKEY points to MFR_TOPIC_T.ID in any active
> message.
>
> When a topic is deleted, the surrogatekey of the messages is set to
> null, so data is orphaned and can be restored in the future as needed.
>
> So I think that these queries show that there is a problem in our
> MFR_MESSAGE_T table...
>
> select count(*) from mfr_message_t
>
> 967616
>
> select count(*) from mfr_message_t
> where surrogateKey is null
>
> 915817
>
> select count(*) from mfr_message_t where id in (
> select messageSurrogateKey from mfr_pvt_msg_usr_t where context_id in
> (select site_id from sakai_site))
> and surrogateKey is null
>
> 307474
>
> There are thousands of messages that are in use in our platform with a
> null SURROGATEKEY in our database. I wonder if this could be the problem
> of the slow queries, specially having that SURROGATEKEY is the field
> where the index is set in MFR_MESSAGE_T table.
>
> We do not know why has happened or how to fix this. Has anybody found
> this issue before?
>
> Thanks in advance.
> Best regards.