List:General Discussion« Previous MessageNext Message »
From:Matthew Blissett Date:December 11 2009 1:00pm
Subject:Join between columns with unique keys randomly uses the keys, or
uses a temporary table (which fails).
View as plain text  
Hi all
 
I have this query:
SELECT n.ID, n.CatalogNumber, [...more...], d.ID, d.CatalogNumber, [...more...]
FROM newdarwincoredata n
INNER JOIN darwincoredata d ON n.CatalogNumber = d.CatalogNumber
ORDER BY n.CatalogNumber;
 
Both tables have exactly the same structure and indices:
mysql> SHOW CREATE TABLE darwincoredata;
CREATE TABLE `darwincoredata` (
  `ID` int(10) NOT NULL auto_increment,
  `CatalogNumber` varchar(20) NOT NULL,
  [...more...],
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `CatalogNumber` (`CatalogNumber`),
  UNIQUE KEY `GlobalUniqueIdentifier` (`GlobalUniqueIdentifier`),
  KEY `DateLastModified` (`DateLastModified`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

There are 336983 rows in darwincoredata, and 337304 in newdarwincoredata.

The plan for the query varies between using the CatalogNumber index and using a temporary table (which fails, once it fills up the disk). I get either this:
mysql> EXPLAIN <that query>
+----+-------------+-------+------+---------------+---------------+---------+------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref                          | rows   | Extra                           |
+----+-------------+-------+------+---------------+---------------+---------+------------------------------+--------+---------------------------------+
|  1 | SIMPLE      | d     | ALL  | CatalogNumber | NULL          | NULL    | NULL                         | 336238 | Using temporary; Using filesort |
|  1 | SIMPLE      | n     | ref  | CatalogNumber | CatalogNumber | 22      | GBIF_wrapper.d.CatalogNumber |      1 |                                 |
+----+-------------+-------+------+---------------+---------------+---------+------------------------------+--------+---------------------------------+
Or this:
+----+-------------+-------+-------+---------------+---------------+---------+------------------------------+--------+-------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref                          | rows   | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------------------------------+--------+-------+
|  1 | SIMPLE      | d     | index | CatalogNumber | CatalogNumber | 22      | NULL                         | 326508 |       |
|  1 | SIMPLE      | n     | ref   | CatalogNumber | CatalogNumber | 22      | GBIF_wrapper.d.CatalogNumber |      1 |       |
+----+-------------+-------+-------+---------------+---------------+---------+------------------------------+--------+-------+
Executing
    SHOW INDEX IN newdarwincoredata; SHOW INDEX IN darwincoredata;
And then doing the EXPLAIN again sometimes changes the plan. I assume this is because the cardinalities are recalculated.

I expect the CatalogNumber unique keys to always be used for this query. Even if I do:
SELECT [...]
FROM newdarwincoredata n FORCE INDEX (CatalogNumber)
INNER JOIN darwincoredata d FORCE INDEX (CatalogNumber) ON n.CatalogNumber = d.CatalogNumber
ORDER BY n.CatalogNumber
The query still sometimes uses a temporary table.

EXPLAIN SELECT STRAIGHT_JOIN [...] FROM newdarwincoredata n INNER JOIN darwincoredata d ON n.CatalogNumber = d.CatalogNumber ORDER BY n.CatalogNumber
+----+-------------+-------+-------+---------------+---------------+---------+------------------------------+--------+-------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref                          | rows   | Extra |
+----+-------------+-------+-------+---------------+---------------+---------+------------------------------+--------+-------+
|  1 | SIMPLE      | n     | index | CatalogNumber | CatalogNumber | 22      | NULL                         | 336588 |       |
|  1 | SIMPLE      | d     | ref   | CatalogNumber | CatalogNumber | 22      | GBIF_wrapper.n.CatalogNumber |      1 |       |
+----+-------------+-------+-------+---------------+---------------+---------+------------------------------+--------+-------+
This seems to always use the index, but I don't want to rely on this without knowing why -- might it be because n has more rows than d?

MySQL version is 5.0.22-log, x86_64, redhat-linux-gnu.

Thanks for any insights

Matt Blissett
Thread
Join between columns with unique keys randomly uses the keys, oruses a temporary table (which fails).Matthew Blissett11 Dec