Emailmessage Table Size

[ajhalls]

I could use a little help understanding the emailmessage table. I was helping an office yesterday, and noticed their emailmessage table was 1.5GB, largest in the database by far, twice the size of the securitylog table. As I looked at the data there, I found messages that were up to 22MB in size, but I also noticed a large amount of junk mail being stored.

Of the 16,000 messages, only 600 had a `patnum` value set. Running the following query:

Code: Select all

select FromAddress, count(FromAddress) from  emailmessage
group by FromAddress
ORDER BY count(FromAddress) DESC

I see 190 emails from BigLots as the top result, so going to gmail, I delete all those messages, clear the trash can, change the Open Dental Sync schedule to every minute, and..... Nothing. Still have 190 messages that are spam that are tying up database space, that aren't associated with a patient, and not sure how to clean that up.

The curious thing is, the Gmail account is using about 8GB of storage, so OpenDental isn't downloading everything, which is nice, but not sure how it is prioritizing what to download, and as it is, it doesn't seem like a sustainable model. Looking at a backup from a couple years ago, they weren't using this feature, so within a couple years their database has grown 1.5GB?

Do they really have to delete spam through OpenDental AND Gmail? Can't we check to see if those messages still exist on the server and remove them if it isn't AND if it isn't assigned to a patient?

Alternatively, delete all messages that are over 30-60 days old that aren't assigned to a patient?

Anyway, just trying to think through what the plan is here as more time goes by.

[jsalmon]

Sounds like you just want Feature Request #3276 - "Incoming Email IMAP Access"
https://www.opendental.com/manual/featurerequests.html