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)