very simple jointure, how to avoid filesort ?

MySQL & MariaDB Other MySQL® Questions
1

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 :wink:

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 )

2

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.