Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : MySQL JOIN and INDEX

[StevenG]

Hello wonder if anyone has any suggestions about this one.

I have an issue with queries to a mailsystem database, not using the INDEX on the `account` table (JOIN on `emailaddress`, `emailaddress` 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. I'm all out of ideas to fix this.

Using latest 5.0.x MySQL version, just for extra info.

Any suggestions gratefully accepted.

[StevenG]

I'll update anyway, appears to be a bug in MySQL since join_buffer was added past 4.x

Anyway, has been added for triage in MySQL 6 apparently, http://bugs.mysql.com/bug.php?id=31446

So, looks like Postgresql might be a better option.