Emails table query slowness

**rgodoy** · 2007-04-03, 07:17 PM

After using sugarcrm for about one year withouth much trouble, than the issues previous versions had, we are facing slowness.

I've been doing some research on the matter and found that queries to the emails table are taking too much time on the database side.

So, for instance this query is the most time consuming.

Code:

Tue Apr  3 13:55:57 2007,211 [16068] FATAL SugarCRM - Slow Query (time:12.718153
SELECT count(*) c FROM emails LEFT JOIN emails_contacts ec ON emails.id = ec.email_id LEFT JOIN contacts ON ec.contact_id = contacts.id  LEFT JOIN users
                                ON emails.assigned_user_id=users.id WHERE emails.assigned_user_id = '8e04cdcb-b053-6f24-2d5f-4497ae072081' AND emails.type = 'inbound' AND emails.status != 'archived' AND  emails.deleted=0

Also this one, which is recurrent since it gets the number of unread emails, so it's used in almost everyplace, this makes the user experience slow on the whole system and not only for email module.

Code:

SELECT count(*) AS c FROM emails WHERE deleted=0 AND assigned_user_id = '8e04cdcb-b053-6f24-2d5f-4497ae072081' AND type = 'inbound' AND status = 'unread'

You may note that all of them have the condition

Code:

deleted = 0

.

In the previous months we've had some issue regarding emails being downloaded multiple times, so I got for instance 1000 copies of one email on my inbox. This was happening with emails being sent from gmail. We use POP for retrieveing email. I didn't have time to investigate more on this, but of course this led us to have a lot of deleted records.

So, after doing some cache-related parameter increasing and other tweakings to the database, like optimise it, we are still facing slowness. There was no better response.

Explaining the queries shows something interesting.

Code:

mysql> explain SELECT count(*) AS c FROM emails WHERE deleted=0 AND assigned_user_id = '8e04cdcb-b053-6f24-2d5f-4497ae072081' AND type = 'inbound' AND status = 'unread';
+----+-------------+--------+------+--------------------+--------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys      | key                | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+--------------------+--------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | emails | ref  | idx_email_assigned | idx_email_assigned |     261 | const,const,const | 8292 | Using where |
+----+-------------+--------+------+--------------------+--------------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

If I remove the deleted condition it uses indexes, and in fact response is much much fast.

Code:

mysql> explain SELECT count(*) AS c FROM emails WHERE assigned_user_id = '8e04cdcb-b053-6f24-2d5f-4497ae072081' AND type = 'inbound' AND status = 'unread';
+----+-------------+--------+------+--------------------+--------------------+---------+-------------------+------+--------------------------+
| id | select_type | table  | type | possible_keys      | key                | key_len | ref               | rows | Extra                    |
+----+-------------+--------+------+--------------------+--------------------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | emails | ref  | idx_email_assigned | idx_email_assigned |     261 | const,const,const | 8292 | Using where; Using index |
+----+-------------+--------+------+--------------------+--------------------+---------+-------------------+------+--------------------------+
1 row in set (0.01 sec)

I wonder if adding an index on a boolean-value field would be worthwhile, Of course deleting those deleted messages would help also, but I consider that non-optimal and short-term solution.

I'll have to set a testing escenary to do more investigation on this but I'm posting this here in order to get more ideas about it.

The other parts of the system work fine but they feel slow due the emails queries.

MySQL version is 4.1 and db engine used is MyISAM. However, I consider this is an application issue rather than db side. If I run other queries on the db the response is good.

**sugarchris** · 2007-04-03, 08:23 PM

We are *very* aware of this situation as we have been using InboundEmail internally since the 4.0 beta days. Jacob, our CTO, has continuously diagnosed this problem and has a solution that is almost ready for primetime. I'll make him aware of this thread; he may pop in to fill in the blanks.

However, this change is touches our schema and metadata layers, so it's not a simple implementation, but rest assured, when 5.0 is released, your email module will perform as well if not better than any other module in Sugar.

I can't say too much here, but Email 2.0 also changes the way emails enter the system and will contribute to ongoing performance enhancements as well.