Dear all,
I have two tables, I want to join:
CREATE TABLE `GD_test2` (
`column1` varchar(60) NOT NULL DEFAULT '',
KEY `i1` (`column1`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `GD_test1` (
`column1` double NOT NULL AUTO_INCREMENT,
`column2` varchar(510) NOT NULL,
`column3` varchar(50) NOT NULL,
`column4` varchar(50) NOT NULL,
`column5` varchar(50) NOT NULL,
`column6` varchar(10) NOT NULL,
`column7` varchar(50) NOT NULL,
PRIMARY KEY (`column1`),
UNIQUE KEY `i1` (`column3`,`column4`,`column7`),
KEY `i2` (`column7`)
) ENGINE=MyISAM AUTO_INCREMENT=50726 DEFAULT CHARSET=utf8
Table status;
SHOW TABLE STATUS LIKE 'GD_test1';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
GD_test1 MyISAM 10 Dynamic 354 110 39248 281474976710655 45056 0 50726 2012-03-02 09:28:54 2012-03-02 09:28:54 2012-03-02 09:28:54 utf8_general_ci NULL
SHOW TABLE STATUS LIKE 'GD_test2';
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
GD_test2 MyISAM 10 Dynamic 247136 20 5025552 281474976710655 1097728 0 NULL 2012-03-02 09:29:54 2012-03-02 09:29:55 2012-03-02 09:29:55 latin1_swedish_ci NULL
Index Statistics:
SHOW INDEX FROM TMP.GD_test1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
GD_test1 0 PRIMARY 1 column1 A 354 NULL NULL BTREE
GD_test1 0 i1 1 column3 A NULL NULL NULL BTREE
GD_test1 0 i1 2 column4 A NULL NULL NULL BTREE
GD_test1 0 i1 3 column7 A 354 NULL NULL BTREE
GD_test1 1 i2 1 column7 A 177 NULL NULL BTREE
SHOW INDEX FROM TMP.GD_test2;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
GD_test2 1 i1 1 column1 A 10745 NULL NULL BTREE
The query is
explain extended
SELECT
1
FROM TMP.GD_test1 t2
JOIN TMP.GD_test2 t1
on t1.column1 = t2.column4
and t2.column7 = 'sth'
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ref i2 i2 152 const 1 100.00 Using where
1 SIMPLE t1 index NULL i1 62 NULL 247136 100.00 Using where; Using index; Using join buffer
And the question:
Why doesn't it use the index for the join (i1 in t2). It knows that it only retrieves 1 value from table t2. But it does a full table scan on table 1. If i select the values manually from t2 and use a where in then the explain plan is as follows:
explain extended
SELECT
1
FROM TMP.GD_test2 t1 -- force index for join (PD_ID)
where t1.column1 in ('a','b','c')
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 range i1 i1 62 NULL 3 100.00 Using where; Using index
Thanks and sorry for the ugly formatting :)
Jan
Edited 1 time(s). Last edit at 03/02/2012 02:54AM by Jan D.