No Body answered my previous mail.
plz help me.

I have three tables.

i) student_info3
i) grade_ex3
i) test_info

and, There are those Index

In student_info3 table : index(student_no)
In grade_ex3 table : index(student_no, test_no)
In test_info table : index(test_no)

When I use this SELECT statment, MySQL use indexes well.

mysql> explain select a.student_no from student_info3 a , grade_ex3 b , test_info c
    -> where a.student_no = b.student_no and b.test_no = c.test_no ;
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
| table | type   | possible_keys | key          | key_len | ref       | rows | Extra   
|                |
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
| a     | index  | student_no    | student_no   |       4 | NULL      |   10 | Using 
|index             |
| b     | index  | student_no_2  | student_no_2 |       8 | NULL      |   12 | where 
|used; Using index |
| c     | eq_ref | PRIMARY       | PRIMARY      |       4 | b.test_no |    1 | Using 
|index             |
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
3 rows in set (0.00 sec)

But When I use this kind of SELECT statement, Table a does not use index anyway.

mysql> explain select a.* from student_info3 a , grade_ex3 b , test_info c
    -> where a.student_no = b.student_no and b.test_no = c.test_no ;
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
| table | type   | possible_keys | key          | key_len | ref       | rows | Extra   
|                |
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
| a     | ALL    | student_no    | NULL         |    NULL | NULL      |   10 |         
|                |
| b     | index  | student_no_2  | student_no_2 |       8 | NULL      |   12 | where 
|used; Using index |
| c     | eq_ref | PRIMARY       | PRIMARY      |       4 | b.test_no |    1 | Using 
|index             |
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
3 rows in set (0.00 sec)

Somebody help me!

here are other information.

mysql> explain select b.korean from student_info3 a , grade_ex3 b , test_info c
    -> where a.student_no = b.student_no and b.test_no = c.test_no ;
+-------+--------+---------------+------------+---------+-----------+------+-------------+
+
| table | type   | possible_keys | key        | key_len | ref       | rows | Extra     
|  |
+-------+--------+---------------+------------+---------+-----------+------+-------------+
+
| a     | index  | student_no    | student_no |       4 | NULL      |   10 | Using 
|index |
| b     | ALL    | student_no_2  | NULL       |    NULL | NULL      |   12 | where 
|used  |
| c     | eq_ref | PRIMARY       | PRIMARY    |       4 | b.test_no |    1 | Using 
|index |
+-------+--------+---------------+------------+---------+-----------+------+-------------+
+


this uses index well.

mysql> explain select c.test_name from student_info3 a , grade_ex3 b , test_info c
    -> where a.student_no = b.student_no and b.test_no = c.test_no ;
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
| table | type   | possible_keys | key          | key_len | ref       | rows | Extra   
|                |
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
| a     | index  | student_no    | student_no   |       4 | NULL      |   10 | Using 
|index             |
| b     | index  | student_no_2  | student_no_2 |       8 | NULL      |   12 | where 
|used; Using index |
| c     | eq_ref | PRIMARY       | PRIMARY      |       4 | b.test_no |    1 |         
|                |
+-------+--------+---------------+--------------+---------+-----------+------+-------------------------+
+
3 rows in set (0.00 sec)




---------------------------------------

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