Hi, For such small tables, does it matter. MySQL will optimise queries the best way it thinks it can. In this case, in the first query, only the index file for student_info3 will be used, so that might have a bearing. In the second query, where all columns from the student table are requested with no restriction on the student_no column, a full table scan is seen as the best way of getting the data. Check the 'How MySQL uses Indexes' section of the manual. Hope this helps Quentin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 20 February 2001 07:30 To: MySQL Maillin List Subject: Index Question(again). 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) The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
