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

[Daniel Merino]

Thanks, Will. You are right, in MFR_MESSAGE_T surrogatekey is null for 
private messages and it only stores a value for forum messages.

It's quite weird for me the way these tables work. It seems that in 
private messages the folder names are stored in MFR_TOPIC_T. But the id 
of those records are not linked with surrogateKey value in MFR_MESSAGE_T 
as I would expect.

Instead that, the folder name for each private message is retrieved from 
CMN_TYPE_T table related to TYPE_UUID value in MFR_PVT_MSG_USR_T table.

This changes a lot the cleaning queries in which I am researching right now.

Thanks for your help.
Best regards.

El 07/11/14 a las 16:31, Will Humphries escribió:
> 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.
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>

-- 
Daniel Merino Echeverría
daniel.merino at unavarra.es
Gestor de E-learning - Centro Superior de Innovación Educativa.
Tfno: 948-168489 - Universidad Pública de Navarra.
--
Para que triunfe el mal, sólo es necesario que los buenos no hagan nada. 
(Edmund Burke)