In the first query, mysqld could get all the information it
needed from table a from the index file.
In the second query, it needed to read the data file to get all
the columns, and determined that using an index would not gain
any speed. From the 3 reow returned, I woul guess that you
don't have enough data in the files to warrant using an index.
ÇãÁ¤¼ö wrote:
>
> 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)
---------------------------------------------------------------------
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
