0

Currently in the initial implementation of our db scheme there is following implementation of email message table:

EMAIL_MESSAGE

id replyOfId forwardOfId subject date
1  NULL      NULL        text    11
2  1         NULL        text    12
3  2         NULL        text    13
4  1         NULL        text    12 // new email thread was started in this row
...

Currently in order to identify threads there is complex logic which should hit db too many times in order to extract child record by replyOfId or forwardOfId

It would be good to know in which way this can be optimized.

I've thought about some approach in order to create additional table with following mapping:

EMAIL_MESSAGE_THREAD
messageId    messageThreadId
1            11
2            11
3            11
1            22
4            22

But not sure whether I really need to go with this approach It would be good to know vision the best way how it can be implemented.

fashuser
  • 2,152
  • 3
  • 29
  • 51
  • https://stackoverflow.com/questions/39917848/recursion-on-database-query-to-get-hierarchical-result-using-hibernate-java - does this help? – Neville Kuyt Sep 14 '17 at 15:58
  • Thanks Neville Kuyt for quick suggestion, is it possible to use this recursive query clause with hibernate ? – fashuser Sep 14 '17 at 16:18
  • https://stackoverflow.com/questions/2486757/hql-recursion-how-do-i-do-this - two options for Hibernate – Neville Kuyt Sep 15 '17 at 08:29

0 Answers0