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)
