Emailmessage Table Size

For users or potential users.
Post Reply
ajhalls
Posts: 36
Joined: Fri Jan 10, 2014 1:41 pm
Location: Utah
Contact:

Emailmessage Table Size

Post by ajhalls » Thu Dec 29, 2022 9:49 am

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.
Dr. Alan Halls DMD
alan@reminderdental.com
ReminderDental.com - A new way to save

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Emailmessage Table Size

Post by jsalmon » Fri Jan 06, 2023 2:54 pm

Sounds like you just want Feature Request #3276 - "Incoming Email IMAP Access"
https://www.opendental.com/manual/featurerequests.html
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Post Reply