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)

Reply via email to