title: SugarCRM Slow Queries (on MySQL)
body: <p>We are using SugarCRM with a MySQL 5.5 database and are seeing poor performance with a frequently occuring query. </p>
  
  <p>Unfortunately re-ordering of the query is not possible due to the nature of SugarCRM. I've attempted optimizations via indexing, but I haven't gotten very far.  That said, I'm not very proficient at doing such either.</p>
  
  <p>Can you suggest any alternate indexing that will improve our results and avoid the filesort?</p>
  
  <p>Query:</p>
  
  <pre><code>SELECT DISTINCT cases.id, cases.case_number, cases.status, cases.name,
     cases.date_entered, 
     cases.assigned_user_id, cases.system_id
  FROM cases
  INNER JOIN team_sets_teams tst ON tst.team_set_id = cases.team_set_id
  INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id
    AND team_memberships.user_id = 'f09ab586-986c-a6f6-0c2e-4d1f1432b6ec' 
    AND team_memberships.deleted=0
  ORDER BY cases.case_number DESC
  LIMIT 0,11;
  </code></pre>
  
  <p>EXPLAIN results:</p>
  
  <pre><code>select_type table             type possible_keys                                   key                 ref                               rows Extra
  SIMPLE      team_memberships  ref  team_id,user_id,idx_team_membership             idx_team_membership const                             26   Using where; Using index; Using temporary; Using filesort
  SIMPLE      tst               ref  idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id idx_ud_team_id      sugarcrm.team_memberships.team_id 7    Using where
  SIMPLE      cases             ref  idx_cases_tmst_id,idx_cases_created             idx_cases_tmst_id   sugarcrm.tst.team_set_id          5    Using where
  </code></pre>
  
  <p>Table Definitions:</p>
  
  <pre><code>CREATE TABLE `cases` (
  `id` char(36) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `description` text,
  `deleted` tinyint(1) DEFAULT '0',
  `assigned_user_id` char(36) DEFAULT NULL,
  `team_id` char(36) DEFAULT NULL,
  `case_number` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `priority` varchar(100) DEFAULT NULL,
  `resolution` text,
  `system_id` int(11) DEFAULT NULL,
  `work_log` text,
  `account_id` char(36) DEFAULT NULL,
  `portal_viewable` tinyint(1) DEFAULT '0',
  `team_set_id` char(36) DEFAULT NULL,
  `parent_id` char(36) DEFAULT NULL,
  `parent_type` varchar(255) DEFAULT 'Cases',
  PRIMARY KEY (`id`),
  UNIQUE KEY `casesnumk` (`case_number`),
  UNIQUE KEY `case_number` (`case_number`,`system_id`),
  KEY `idx_case_name` (`name`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_cases_stat_del` (`assigned_user_id`,`status`,`deleted`),
  KEY `idx_cases_tmst_id` (`team_set_id`),
  KEY `date_modified` (`date_modified`),
  KEY `modified_user_id` (`modified_user_id`),
  KEY `idx_cases_created` (`team_set_id`,`date_entered`),
  KEY `team_id` (`team_id`),
  KEY `idx_cases_del` (`deleted`),
  KEY `idx_cases_date_entered` (`date_entered`),
  KEY `idx_cases_status` (`status`),
  KEY `idx_cases_parent_id` (`parent_id`),
  KEY `idx_cases_priority` (`priority`)
  ) ENGINE=InnoDB
  
  CREATE TABLE `team_sets_teams` (
  `id` char(36) NOT NULL,
  `team_set_id` char(36) DEFAULT NULL,
  `team_id` char(36) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_ud_set_id` (`team_set_id`,`team_id`),
  KEY `idx_ud_team_id` (`team_id`),
  KEY `idx_ud_team_set_id` (`team_set_id`),
  KEY `idx_tst_deleted` (`deleted`)
  ) ENGINE=InnoDB
  
  CREATE TABLE `team_memberships` (
  `id` char(36) NOT NULL,
  `team_id` char(36) DEFAULT NULL,
  `user_id` char(36) DEFAULT NULL,
  `explicit_assign` tinyint(1) DEFAULT '0',
  `implicit_assign` tinyint(1) DEFAULT '0',
  `date_modified` datetime DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `date_modified` (`date_modified`),
  KEY `team_id` (`team_id`),
  KEY `user_id` (`user_id`),
  KEY `idx_team_membership` (`user_id`,`team_id`,`deleted`)
  ) ENGINE=InnoDB
  </code></pre>