Hi,

I'm having slow downs with returning some account detail views. I've narrowed it down to returning the 11 records for our email subpanel (based on the history subpanel). For one account, it's taking over 12 seconds to generate the subpanel contents.

The email subpanel is just a copy of the history subpanel but with the metadata for calls, meetings and tasks removed.

Now, I copied the installation and database to test setup and it's behaving properly on there?!?! Less than a second?

The only difference is that on the live system, it's running innodb and on the test system it's myisam.

The installation is using mysql 5.0.22 so I don't have access to a profiler.

The actual query that the subpanel is generating is:

Code:
(SELECT  emails.id , emails.date_sent  as date_modified , emails.name , emails.status , ' ' contact_name , '  ' contact_id , ' ' contact_name_owner , '' contact_name_mod , emails.parent_id , emails.parent_type  , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id  , 'emails' panel_name  FROM emails INNER JOIN team_memberships team_memberships                                 ON emails.team_id = team_memberships.team_id                                 AND team_memberships.user_id = 'a38b8623-f630-f6ae-76ad-4901d2008c74'                                 AND team_memberships.deleted=0   LEFT JOIN  users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0  AND jt0.deleted=0 INNER JOIN emails_beans ON (emails.id=emails_beans.email_id AND emails_beans.bean_id= '79bc64be-518b-7d83-2eee-4785ffdb3b09' AND bean_module='Accounts') where (  emails_beans.deleted=0 AND emails.deleted=0) AND emails.deleted=0 ) UNION ALL (  SELECT  emails.id , emails.date_sent  as date_modified , emails.name , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type  , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner  , 'Users ' assigned_user_name_mod, ' ' filename , emails.assigned_user_id  , 'linkedemails' panel_name  FROM emails INNER JOIN team_memberships team_memberships                                 ON emails.team_id = team_memberships.team_id                                 AND team_memberships.user_id = 'a38b8623-f630-f6ae-76ad-4901d2008c74'                                 AND team_memberships.deleted=0   LEFT JOIN  users jt0 ON jt0.id= emails.assigned_user_id AND jt0.deleted=0  AND jt0.deleted=0 JOIN (select distinct email_id from emails_email_addr_rel eear         join email_addr_bean_rel eabr on eabr.bean_id ='79bc64be-518b-7d83-2eee-4785ffdb3b09' and eabr.bean_module = 'Accounts' and         eabr.email_address_id = eear.email_address_id and eabr.deleted=0         where eear.deleted=0 and eear.email_id not in         (select eb.email_id from emails_beans eb where eb.bean_module ='Accounts' and eb.bean_id = '79bc64be-518b-7d83-2eee-4785ffdb3b09' and eb.deleted=0)         ) derivedemails on derivedemails.email_id = emails.id where emails.deleted=0 ) ORDER BY date_modified  desc LIMIT 0,11
and if I 'explain' that query I get:

Code:
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: team_memberships
         type: ref
possible_keys: idx_teamuser_del,idx_team_membership,idx_teammemb_team_user
          key: idx_teamuser_del
      key_len: 111
          ref: const,const
         rows: 3
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: emails
         type: ref
possible_keys: PRIMARY,idx_email_team_status,idx_emails_team_id_cstm
          key: idx_email_team_status
      key_len: 109
          ref: sugarcrm.team_memberships.team_id
         rows: 55
        Extra: Using where

*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: emails_beans
         type: ref
possible_keys: idx_emails_beans_bean_id,idx_emails_beans_email_bean
          key: idx_emails_beans_email_bean
      key_len: 223
          ref: sugarcrm.emails.id,const,const
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: jt0
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 108
          ref: sugarcrm.emails.assigned_user_id
         rows: 1
        Extra:
*************************** 5. row ***************************
           id: 2
  select_type: UNION
        table: <derived3>
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 0
        Extra: const row not found
*************************** 6. row ***************************
           id: 2
  select_type: UNION
        table: emails
         type: eq_ref
possible_keys: PRIMARY,idx_email_team_status,idx_emails_team_id_cstm
          key: PRIMARY
      key_len: 108
          ref: const
         rows: 1
        Extra: Using where

*************************** 7. row ***************************
           id: 2
  select_type: UNION
        table: team_memberships
         type: ref
possible_keys: idx_teamuser_del,idx_team_membership,idx_teammemb_team_user
          key: idx_team_membership
      key_len: 218
          ref: const,sugarcrm.emails.team_id
         rows: 1
        Extra: Using where

*************************** 8. row ***************************
           id: 2
  select_type: UNION
        table: jt0
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 108
          ref: sugarcrm.emails.assigned_user_id
         rows: 1
        Extra:
*************************** 9. row ***************************
           id: 3
  select_type: DERIVED
        table: eabr
         type: ref
possible_keys: idx_email_address_id,idx_bean_id
          key: idx_bean_id
      key_len: 185
          ref:
         rows: 1
        Extra: Using where; Using temporary
*************************** 10. row ***************************
           id: 3
  select_type: DERIVED
        table: eear
         type: ref
possible_keys: idx_eearl_address_id
          key: idx_eearl_address_id
      key_len: 108
          ref: sugarcrm.eabr.email_address_id
         rows: 9
        Extra: Using where
*************************** 11. row ***************************
           id: 4
  select_type: DEPENDENT SUBQUERY
        table: eb
         type: index_subquery
possible_keys: idx_emails_beans_bean_id,idx_emails_beans_email_bean
          key: idx_emails_beans_email_bean
      key_len: 223
          ref: func,const,const
         rows: 2
        Extra: Using index; Using where
*************************** 12. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using filesort
12 rows in set (0.00 sec)

I also want to add that the email tables are huge - due to our auto-capture of emails.

emails:801000 rows
emails_beans: 2040000 rows
emails_email_addr_rel: 4460000 rows


Any thoughts on where to start?

Thanks