JOINS AND INDEX NOT USED

[Steve Gore]

Hello everyone,

I have an issue with queries to our mailsystem database, not using the INDEX on the `account` table (JOIN on emailaddress, which is a MERGE table), effectively doing a huge account table scan on each query - there are 3 queries per mail delivery, so this does slow things down a lot, especially when the cpu is busy.

As an example, the following query, shows the account table scan

possible_keys: PRIMARY,accountnumber
key: NULL
key_len: NULL
ref: NULL
rows: 162575

There is an index on this table, but it's never used. I have tried to add USE and FORCE INDEX but returns the same.

> explain SELECT server.mailhome
  FROM (emailaddress INNER JOIN account USING (accountnumber)) 
  LEFT JOIN server ON account.server_current = server.id 
  WHERE emailaddress.domain = 'domain.com'
  AND    ( emailaddress.lhs = '' 
  OR emailaddress.lhs = 'emailuser' ) 
  ORDER BY emailaddress.lhs DESC LIMIT 1;

Shows the accountnumber lookup, not using the index

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emailaddress
         type: range
possible_keys: dbmail_address,new_address,accountnumber
          key: new_address
      key_len: 192
          ref: NULL
         rows: 4
        Extra: Using where; Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: account
         type: ALL
possible_keys: PRIMARY,accountnumber
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 162575
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: server
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: mailsystem.account.server_current
         rows: 1
        Extra:

The tables are all indexed, here is the schema of account, emailaddress. The emailaddress table is a MERGE table, with the 2 underlying tables emailaddress_ispdomain and emailaddress_domain both indexed identically.

CREATE TABLE `account` (
  `accountnumber` char(12) NOT NULL default '',
  `username` char(32) default NULL,
  `code` char(12) NOT NULL default '',
  `password` char(32) NOT NULL default '',
  `dbmail_username` char(16) NOT NULL,
  `preferred_webmail` smallint(5) unsigned default '0',
  `server_requested` smallint(5) unsigned default NULL,
  `server_current` smallint(5) unsigned NOT NULL default '4',
  `server_former` smallint(5) unsigned NOT NULL,
  `class_current` smallint(5) unsigned default NULL,
  `class_requested` smallint(5) unsigned default NULL,
  `modified` datetime default NULL,
  `created` datetime default '0000-00-00 00:00:00',
  `stoptimestamp` datetime default '0000-00-00 00:00:00',
  `last_accessed_imap` datetime default '0000-00-00 00:00:00',
  `last_accessed_pop` datetime default '0000-00-00 00:00:00',
  `last_accessed_webmail_standard` datetime default '0000-00-00 00:00:00',
  `last_accessed_webmail_advanced` datetime default '0000-00-00 00:00:00',
  `token` varchar(13) NOT NULL default '-',
  `filter` mediumtext,
  `pc_code` char(12) default NULL,
  `pc_name` char(64) default NULL,
  `pc_domainsupport` tinyint(4) default NULL,
  PRIMARY KEY  (`accountnumber`),
  KEY `modified` (`modified`),
  KEY `dbmail_username` (`dbmail_username`,`passwordCrypt`),
  KEY `server_current` (`server_current`),
  KEY `accountnumber` (`accountnumber`),
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `emailaddress` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `lhs` char(64) NOT NULL,
  `domain` char(128) NOT NULL,
  `accountnumber` int(10) unsigned NOT NULL,
  `dbmailusername` char(32) NOT NULL,
  `forwardaddress` char(128) default NULL,
  `active` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `dbmail_address` (`lhs`,`domain`,`dbmailusername`),
  UNIQUE KEY `new_address` (`lhs`,`domain`,`accountnumber`),
  KEY `accountnumber` (`accountnumber`),
  KEY `dbmailusername_index` (`dbmailusername`),
  KEY `active` (`active`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`emailaddress_ispdomain`,`emailaddress_domain`)

Does anyone have any suggestions to help solve the account table INDEX not being used when querying a JOIN of account and emailaddress.

Any suggestions gratefully accepted.



Edited 2 time(s). Last edit at 07/30/2009 05:00PM by Steve Gore.