Re: Is it possible to avoid "Using temporary" when distinct is used with join?

[Wai Yip Lau]

Sorry for my late reply:

Create Table: CREATE TABLE `consumers` (
  `consumer_id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `address1` varchar(255) NOT NULL,
  `address2` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `zip` varchar(10) NOT NULL,
  `country` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `date_purchased` datetime NOT NULL,
  `data_source` varchar(255) NOT NULL,
  `link_id` char(32) NOT NULL,
  `phone` char(12) default NULL,
  `last_modified` datetime NOT NULL,
  `merchant_ref` int(10) unsigned NOT NULL,
  `transaction_ref` int(10) unsigned NOT NULL,
  PRIMARY KEY  USING BTREE (`consumer_id`),
  KEY `email` (`email`),
  KEY `link_index` (`link_id`),
  KEY `transaction_ref_index` (`transaction_ref`),
  KEY `merchant_ref_index` USING BTREE (`merchant_ref`,`date_purchased`)
) ENGINE=MyISAM AUTO_INCREMENT=503678 DEFAULT CHARSET=latin1

Create Table: CREATE TABLE `consumer_opts` (
  `consumer_opt_id` int(10) unsigned NOT NULL auto_increment,
  `consumer_ref` int(10) unsigned NOT NULL,
  `opt_ref` int(10) unsigned NOT NULL,
  `value` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  USING BTREE (`consumer_opt_id`),
  KEY `consumer_opt_index` USING BTREE (`consumer_ref`)
) ENGINE=MyISAM AUTO_INCREMENT=503676 DEFAULT CHARSET=latin1

mysql> SHOW TABLE STATUS LIKE 'consumers'\G
*************************** 1. row ***************************
           Name: consumers
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 32
    Data_length: 64
Max_data_length: 281474976710655
   Index_length: 7168
      Data_free: 0
 Auto_increment: 503678
    Create_time: 2010-02-25 15:48:45
    Update_time: 2010-02-25 16:17:33
     Check_time: 2010-02-25 15:49:08
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 

mysql> SHOW TABLE STATUS LIKE 'consumer_opts'\G
*************************** 1. row ***************************
           Name: consumer_opts
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 6
 Avg_row_length: 14
    Data_length: 84
Max_data_length: 3940649673949183
   Index_length: 3072
      Data_free: 0
 Auto_increment: 503676
    Create_time: 2010-02-25 15:26:53
    Update_time: 2010-02-25 16:19:09
     Check_time: 2010-02-25 15:26:57
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 

mysql> EXPLAIN SELECT DISTINCT(consumer_id) FROM consumers INNER JOIN consumer_opts ON consumer_id=consumer_ref; 
+----+-------------+---------------+-------+--------------------+--------------------+---------+-------------------------------------+------+------------------------------+
| id | select_type | table         | type  | possible_keys      | key                | key_len | ref                                 | rows | Extra                        |
+----+-------------+---------------+-------+--------------------+--------------------+---------+-------------------------------------+------+------------------------------+
|  1 | SIMPLE      | consumers     | index | PRIMARY            | PRIMARY            | 4       | NULL                                |    2 | Using index; Using temporary | 
|  1 | SIMPLE      | consumer_opts | ref   | consumer_opt_index | consumer_opt_index | 4       | consumer_data.consumers.consumer_id |    1 | Using index; Distinct        | 
+----+-------------+---------------+-------+--------------------+--------------------+---------+-------------------------------------+------+------------------------------+

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------+----------+
| Variable_name                 | Value    |
+-------------------------------+----------+
| bulk_insert_buffer_size       | 8388608  | 
| innodb_buffer_pool_awe_mem_mb | 0        | 
| innodb_buffer_pool_size       | 8388608  | 
| innodb_log_buffer_size        | 1048576  | 
| join_buffer_size              | 131072   | 
| key_buffer_size               | 16777216 | 
| myisam_sort_buffer_size       | 8388608  | 
| net_buffer_length             | 16384    | 
| preload_buffer_size           | 32768    | 
| read_buffer_size              | 131072   | 
| read_rnd_buffer_size          | 262144   | 
| sort_buffer_size              | 2097144  | 
+-------------------------------+----------+

Thanks