Very simple self-join, but indecent ignored?

advertisements

Got a noob question. Say I create the following table:

temp1

up, varchar(15)

dn, varchar(15)

and I add a couple of indeces:

create table temp1 (up varchar(15), dn varchar(15), index id1(up), index id2(dn))

After I populate the table with some random data, I do the following explain select

explain select * from temp1 as t1, temp1 as t2 where t1.up = t2.up

and get

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | id1           | NULL | NULL    | NULL |    4 |             |
|  1 | SIMPLE      | t2    | ALL  | id1           | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Why isn't the optimizer using the keys?! I must be missing something very simple . . .

(I'm asking this question because a similar query with the tables I'm actually using (700K rows) is running awfully slow, and I'm guessing it has to do with indeces).

Thanks for the help!


Since you select all the rows from temp t1 (and almost all from t2) - mysql decides to use fullscan, due to it is more suitable in such case.