GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

very simple jointure, how to avoid filesort ?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • very simple jointure, how to avoid filesort ?

    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 )

  • #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.

    Comment

    Working...
    X