Hi~ All.
I have a question about using index.
I have two following tables.
mysql> explain ex1 ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | char(10) | YES | MUL | NULL | |
| b | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> explain ex2 ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | char(15) | YES | MUL | NULL | |
| c | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
When I use following queries, strange thing happen.
mysql> explain select ex1.a from ex1, ex2 where ex1.a = ex2.a ;
+-------+-------+---------------+------+---------+------+------+-------------------------+
+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+-------+---------------+------+---------+------+------+-------------------------+
+
| ex1 | index | a | a | 10 | NULL | 10 | Using index
| |
| ex2 | index | a | a | 15 | NULL | 10 | where used; Using
|index |
+-------+-------+---------------+------+---------+------+------+-------------------------+
+
2 rows in set (0.00 sec)
mysql> explain select * from ex1, ex2 where ex1.a = ex2.a ;
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+------------+
| ex1 | ALL | a | NULL | NULL | NULL | 10 | |
| ex2 | ALL | a | NULL | NULL | NULL | 10 | where used |
+-------+------+---------------+------+---------+------+------+------------+
2 rows in set (0.00 sec)
As you can see, First query uses index, but second query does not.
And third query is ....
mysql> explain select ex2.c from ex1, ex2 where ex1.a = ex2.a ;
+-------+-------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+------+---------+------+------+-------------+
| ex1 | index | a | a | 10 | NULL | 10 | Using index |
| ex2 | ALL | a | NULL | NULL | NULL | 10 | where used |
+-------+-------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
Do I make selecting part's columns to INDEX?
What's differ?
Thank you for advanced answer!
---------------------------------------
Member of N.N.R(New Network Research)
Visit NNR.OR.KR
-------------------------------------------------------
엠파스가 만든 팔팔한 메일, 엠팔 (http://www.empal.com)
문장으로 찾는 검색엔진, 엠파스 (http://www.empas.com)
신나는 생활문화정보, 시티스케이프 (http://www.cityscape.co.kr)
