MySQL Forums
Forum List  »  Optimizer & Parser

how to avoir filesort and temporary with theses 2 little tables ?
Posted by: yannick simon
Date: September 07, 2010 06:33AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
how to avoir filesort and temporary with theses 2 little tables ?
2167
September 07, 2010 06:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.