# very simple jointure, how to avoid filesort ?

**URL:** https://forums.percona.com/t/very-simple-jointure-how-to-avoid-filesort/1500
**Category:** Other MySQL® Questions
**Created:** [September 7, 2010, 6:35am UTC](https://forums.percona.com/t/very-simple-jointure-how-to-avoid-filesort/1500 "2010-09-07T06:35:25Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![ysimon](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ysimon/32/837_2.png) [@ysimon](https://forums.percona.com/u/ysimon)
#### Post date: [September 7, 2010, 6:35am UTC](https://forums.percona.com/t/very-simple-jointure-how-to-avoid-filesort/1500/1 "2010-09-07T06:35:25Z")

</div>

Hello

i don’t understand why with these 2 tables , theses indices (covering the columns … ), i still have a using temporary and a using filesort, can you help me please ?

thank you 😉

CREATE TABLE tableA (  
`key` int(11) NOT NULL,  
`value` int(11) NOT NULL,  
pk int(11) NOT NULL AUTO\_INCREMENT,  
PRIMARY KEY (pk),  
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,  
pk int(11) NOT NULL AUTO\_INCREMENT,  
PRIMARY KEY (pk),  
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

Thank you for your help )

---

<div class="post-metadata">

### Author: ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)
#### Post date: [September 7, 2010, 1:18pm UTC](https://forums.percona.com/t/very-simple-jointure-how-to-avoid-filesort/1500/2 "2010-09-07T13:18:37Z")

</div>

GROUP BY works just as ORDER BY. There are two ways of executing your query:

1. retrieve values of tblA ordered by value, and for each record, find the matching records in tblB and see if they satisfy value=20.

2. retrieve values of tblB for which value=20, and for each record, find the matching records in tblA. Then sort all rows.

The number of rows in tblB satisfying val=20 will determine which method is fastest.