how to avoir filesort and temporary with theses 2 little tables ?

[yannick simon]

Hello

i don't manage to solve my issue. It seems to be very simple, but, i don't manage to improve my query

i've got 2 tables like this

CREATE TABLE IF NOT EXISTS `tableA` (
`key` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`key`,`value`),
KEY `Avalue` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



CREATE TABLE IF NOT EXISTS `tableB` (
`key` int(11) NOT NULL,
`value` int(11) NOT NULL,
PRIMARY KEY (`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);


when i try to explain my query, i've got something strange ... the "using temporary" and "using filesort"


EXPLAIN SELECT tableA.key
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

can you help me please ?

(note : i really want the pk to be 2 fields "key and value" for these tables as they are simplified examples for my real problem)


Thank you