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

[Daniel Merino]

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.
-- 
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.