views:

8

answers:

0
CREATE TABLE tableA (
`key` int(11) NOT NULL,
`value` int(11) NOT NULL,
UNIQUE KEY Akeyvalue (`key`,`value`),
KEY Avalue (`value`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

CREATE TABLE tableB (
`key` int(11) NOT NULL,
`value` int(11) NOT NULL,
UNIQUE KEY Bkeyvalue (`key`,`value`),
 KEY Bvalue (`value`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;



INSERT INTO `tableA` (`key`, `value`) VALUES
(1, 20),
(1, 30),
(1, 40);


INSERT INTO `tableB` (`key`, `value`) VALUES
(1, 20),
(1, 30),
(1, 40) ;

EXPLAIN SELECT tableA.value
FROM tableA
INNER JOIN tableB ON tableA.key = tableB.key
WHERE tableB.value =20
GROUP BY tableA.value

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE tableB ref Bkeyvalue,Bvalue Bvalue 4 const 1 Using temporary; Using filesort

1 SIMPLE tableA ref Akeyvalue Akeyvalue 4 test.tableB.key 1 Using index

So, you can see, i have many indexes... i don't understand

Thank you for your help