Imap gmail slow

isaacmarco · 27 January 2022 13:51

I’ve upgraded a suitecrm 7.6.7 to 7.12.2 and users complain when using the email module to see their mails in gmail because it is too slow (more than 50 seconds to get 75 email headers).

Every user config his profile to connect with gmail mailboxes via imap.

Debugging the problem I see many slow queries to the email table.

Wed Jan 26 17:45:27 2022 [772865][1][FATAL] Slow Query (time:0.93900990486145
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7770' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:28 2022 [772865][1][FATAL] Slow Query (time:0.91093397140503
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7770' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:29 2022 [772865][1][FATAL] Slow Query (time:0.88172602653503
SELECT emails.* FROM emails  WHERE uid = '7770' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:30 2022 [772865][1][FATAL] Slow Query (time:0.90342378616333
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7769' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:31 2022 [772865][1][FATAL] Slow Query (time:0.9082670211792
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7769' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:32 2022 [772865][1][FATAL] Slow Query (time:0.90567803382874
SELECT emails.* FROM emails  WHERE uid = '7769' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:33 2022 [772865][1][FATAL] Slow Query (time:0.91213297843933
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7768' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:34 2022 [772865][1][FATAL] Slow Query (time:0.91259098052979
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7768' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:34 2022 [772865][1][FATAL] Slow Query (time:0.86886286735535
SELECT emails.* FROM emails  WHERE uid = '7768' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:35 2022 [772865][1][FATAL] Slow Query (time:0.92277789115906
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7767' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:36 2022 [772865][1][FATAL] Slow Query (time:0.91139602661133
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7767' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:37 2022 [772865][1][FATAL] Slow Query (time:0.85950088500977
SELECT emails.* FROM emails  WHERE uid = '7767' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:38 2022 [772865][1][FATAL] Slow Query (time:0.88885688781738
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7766' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:39 2022 [772865][1][FATAL] Slow Query (time:0.89524793624878
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7766' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:40 2022 [772865][1][FATAL] Slow Query (time:0.86539602279663
SELECT emails.* FROM emails  WHERE uid = '7766' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:41 2022 [772865][1][FATAL] Slow Query (time:0.89419794082642
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7765' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:42 2022 [772865][1][FATAL] Slow Query (time:0.90347385406494
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7765' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:42 2022 [772865][1][FATAL] Slow Query (time:0.85796904563904
SELECT emails.* FROM emails  WHERE uid = '7765' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:43 2022 [772865][1][FATAL] Slow Query (time:0.88981294631958
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7764' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:44 2022 [772865][1][FATAL] Slow Query (time:0.91700506210327
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7764' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:45 2022 [772865][1][FATAL] Slow Query (time:0.86620497703552
SELECT emails.* FROM emails  WHERE uid = '7764' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:46 2022 [772865][1][FATAL] Slow Query (time:0.9022319316864
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7763' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:47 2022 [772865][1][FATAL] Slow Query (time:0.93136787414551
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7763' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:48 2022 [772865][1][FATAL] Slow Query (time:0.8842179775238
SELECT emails.* FROM emails  WHERE uid = '7763' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:49 2022 [772865][1][FATAL] Slow Query (time:0.91284418106079
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7762' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:50 2022 [772865][1][FATAL] Slow Query (time:0.90905714035034
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7762' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:51 2022 [772865][1][FATAL] Slow Query (time:0.87403798103333
SELECT emails.* FROM emails  WHERE uid = '7762' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:51 2022 [772865][1][FATAL] Slow Query (time:0.92159295082092
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7761' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:52 2022 [772865][1][FATAL] Slow Query (time:0.91684222221375
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7761' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:53 2022 [772865][1][FATAL] Slow Query (time:0.876620054245
SELECT emails.* FROM emails  WHERE uid = '7761' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:54 2022 [772865][1][FATAL] Slow Query (time:0.90852785110474
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7760' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:55 2022 [772865][1][FATAL] Slow Query (time:0.89805102348328
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7760' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:56 2022 [772865][1][FATAL] Slow Query (time:0.85335993766785
SELECT emails.* FROM emails  WHERE uid = '7760' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:57 2022 [772865][1][FATAL] Slow Query (time:0.88656282424927
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7759' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:58 2022 [772865][1][FATAL] Slow Query (time:0.88611197471619
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7759' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:45:59 2022 [772865][1][FATAL] Slow Query (time:0.85839080810547
SELECT emails.* FROM emails  WHERE uid = '7759' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:45:59 2022 [772865][1][FATAL] Slow Query (time:0.88148593902588
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7758' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:00 2022 [772865][1][FATAL] Slow Query (time:0.88884615898132
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7758' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:01 2022 [772865][1][FATAL] Slow Query (time:0.92806506156921
SELECT emails.* FROM emails  WHERE uid = '7758' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:46:01 2022 [777378][1][WARN] There is no any workflow to run
 
Wed Jan 26 17:46:02 2022 [772865][1][FATAL] Slow Query (time:0.98910808563232
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7757' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:03 2022 [772865][1][FATAL] Slow Query (time:0.89977288246155
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7757' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:04 2022 [772865][1][FATAL] Slow Query (time:0.86867809295654
SELECT emails.* FROM emails  WHERE uid = '7757' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:46:05 2022 [772865][1][FATAL] Slow Query (time:0.8966600894928
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7756' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:06 2022 [772865][1][FATAL] Slow Query (time:0.89115500450134
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7756' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:07 2022 [772865][1][FATAL] Slow Query (time:0.86240315437317
SELECT emails.* FROM emails  WHERE uid = '7756' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:46:08 2022 [772865][1][FATAL] Slow Query (time:0.88788414001465
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7755' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:08 2022 [772865][1][FATAL] Slow Query (time:0.92219400405884
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7755' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:09 2022 [772865][1][FATAL] Slow Query (time:0.87941598892212
SELECT emails.* FROM emails  WHERE uid = '7755' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:46:10 2022 [772865][1][FATAL] Slow Query (time:0.91070795059204
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7754' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:11 2022 [772865][1][FATAL] Slow Query (time:0.89181303977966
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7754' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:12 2022 [772865][1][FATAL] Slow Query (time:0.86645483970642
SELECT emails.* FROM emails  WHERE uid = '7754' AND deleted=0 LIMIT 0,1)
 
Wed Jan 26 17:46:13 2022 [772865][1][FATAL] Slow Query (time:0.89633011817932
SELECT emails.*, users.user_name as assigned_user_name
 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7753' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:14 2022 [772865][1][FATAL] Slow Query (time:0.91510581970215
SELECT emails.*, users.user_name as assigned_user_name or uid columns. Should I create them?

Are I missing some step in the migration proccess?

 FROM emails
 LEFT JOIN users ON emails.assigned_user_id=users.id
WHERE emails.uid LIKE '7753' AND emails.mailbox_id = '22383af8-a754-c23d-5298-50b8fff22e0f' AND  emails.deleted=0
 ORDER BY date_sent_received DESC)
 
Wed Jan 26 17:46:15 2022 [772865][1][FATAL] Slow Query (time:0.86342215538025
SELECT emails.* FROM emails  WHERE uid = '7753' AND deleted=0 LIMIT 0,1)

I have one million records on the email table and I don’t see index created to assigned_user_id or uid. Should I create index for these columns?

Maybe I skip some step on the migration process… Could you help me?

pgr · 27 January 2022 14:54

One million records on the emails table is a lot. Do you really need all that email inside the CRM? Sometimes people find clever ways of limiting the email that comes into the CRM, use folders so you can leave it on the server if you don’t really need to import it, or clean-up old stuff, etc.

isaacmarco · 27 January 2022 15:58

Well… we use SuiteCRM from 2009 (from sugarcrm). All emails in this table are mails that users considered were relevant for their leads. Users just import from gmail mails they consider important. I suppose other mails will be those that users sent to the leads with email templates from the leads detail screen.

I haven’t a good criteria to remove emails because I could remove relevant emails.

One million of emails are a lot… but… Why is SuiteCRM quering so many times to the mails table with the same mailbox_id? I don’t understand why that is required when users just want to read their gmail inbox in order to decide if they need to import some emails related with their leads.

If so many queries are really required… may be some extra index is missing in this table.

Many of the queries are identical and repeated… and all of them referenced to the same mailbox_id… maybe we could get the same with just one query.

I think that something could be optimized.

Nobody else is dealing with one million of records in the email table? I think my company is medium-low size… many years using the same CRM but not a big company. We just receive 200 new leads every day…

In SuiteCRM 7.6.7 the same user can access to his gmail inbox mails in a few seconds… whit the same million record in the email table. I think something is wrong in newer SuiteCRM version with this.

pgr · 27 January 2022 16:07

Try the indexes. They might make a substantial difference in performance. There are many other performance things you can try (sometimes all the server needs is a bit more memory, for example, or a faster disk).

But none of those things will decrease the number of queries made. You might be right, some of that might be useless work, something that needs to be improved in SuiteCRM.

Still, I advise you to consider more carefully your clean-up options. You should be able to get some criteria going. Like: emails older than 5 years, or emails with leads that haven’t bought from you in the past 3 years, or whatever.

Email is a messy business and it always tends to spin out of control. Gmail is really not an appropriate comparison because that is a whole different scale. In fact, it’s the cloud email services that made us all lazy in our handling of internally stored email… and created expectations for speed and capacity that are very hard to fulfill.

pgr · 27 January 2022 16:30

Oh, and I almost forgot

Remember the basics:

delete rows that have “deleted=1”
delete rows that are orphaned (nothing refers to them, or they don’t refer to anything still present in the system).

This is because sometimes most of the stuff taking up space in the database is really 100% useless. Emails you already deleted, or belonging to accounts and contacts you already deleted.

isaacmarco · 27 January 2022 17:15

Thank you @pgr. Very few records with deleted=1 where found. I suppose this is cleaned with the monthly schedule for this job.

Adding this index dramatically increase performance for me in my environment.

CREATE INDEX idx_email_uid ON emails(uid);

The same user now loads his inbox in just 1 second (before 54 seconds). Obviously the 54 slow queries in my first comment are still been run but now they are fast. I still think many of them are not required but I haven’t enough knowledge with this. I think that I will open an issue in the github tracker for this.

I have created the index directly in the database but I don’t know if I should declare the index in some vardef file in the custom directory… is it possible?

Docs talking about vardefs (Vardefs :: SuiteCRM Documentation) show how to add indices in /module/emails/vardefs.php , but this is a core module so I think this could be rewritten in future upgrades…

Any way to do the same in the custom directory or is OK to do it just in MariaDB CLI?

Thank you.

DJuser · 27 January 2022 17:26

your indexes do look different to mine: I have one for ‘assigned_user_id’. But none for uid

DJuser · 27 January 2022 17:29

Also - email handling changed - in the past 100% of inbound emails were saved in the tables.

Since 7.9 NO inbound emails are saved in tables - UNLESS the user clicks ‘IMPORT’ for them. The user can still see their INBOX emails - which are pulled live over IMAP.

Text from the page below:

Prior to version 7.9, **SuiteCRM** had to synchronise the email headers before users could view them. As of version 7.9, **SuiteCRM** contacts the email server directly - retrieving only the minimal information needed to process an operation. This eliminates the synchronisation process and significantly reduces amount of email information needed to be stored in the database.

Note to developers and administrators: The 'email_cache' table is deprecated in **SuiteCRM** 7.9.

DJuser · 27 January 2022 17:32

If your users are sales people - generally they SEND a lot more emails than RECEIVE.

If they are customer support - it could be evenly balanced.

If they are sales people who used the Campaign modules to send emails… then it can be worse 100 to 1 SENT to RECEIVED. Do a query to look at email subject-lines: bulk campaign emails will have many with the same text; on the same date.

pgr · 28 January 2022 10:48

@isaacmarco great to hear it had such a positive impact. I agree it would be nice to put this into the core product, it seems like a generic need.

For now, you can put your index in the vardefs. When you use the Extension Mechanism, changes from several sources are combined and merged, and they are upgrade-safe.

Remember to run a QR&R, and scroll down to bottom to execute the query making changes to the database (although maybe in your case they are already there).

PeterFr · 14 June 2023 23:55

I am not sure what or where Version 7.9 is, however, I have just installed 7.13 and would like all emails to be cached (stored in MariaDB tables). Is this possible?