MySQL Forums
Forum List  »  Optimizer & Parser

Re: Is it possible to avoid "Using temporary" when distinct is used with join?
Posted by: Wai Yip Lau
Date: March 02, 2010 09:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Is it possible to avoid "Using temporary" when distinct is used with join?
2127
March 02, 2010 09:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.