MySQL et non pas à l'aide de l'index avec une JOINTURE, where et ORDER

Nous avons deux tables qui ressemble à un simple tag-la structure de l'enregistrement comme suit (en réalité c'est beaucoup plus complexe, mais c'est l'essence même du problème):

tag (A.a) | recordId (A.b)
1         | 1
2         | 1
2         | 2
3         | 2
....

et

recordId (B.b) | recordData (B.c)
1              | 123
2              | 666
3              | 1246

Le problème est l'extraction commandé des records avec une étiquette spécifique. Le moyen le plus évident de le faire est avec une simple jointure et l'index sur (PK)(A. a, A. b), (A. b), (PK)(B. b), (b,B. c) en tant que tel:

select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 order by c;

Cependant, cela donne la désagréable résultat d'un filesort:

+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                                        |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+
|  1 | SIMPLE      | A     | ref  | PRIMARY,b     | PRIMARY | 4       | const     |   94 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | B     | ref  | PRIMARY,b     | b       | 4       | booli.A.b |    1 | Using index                                  | 
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+

À l'aide d'une énorme et extrêmement redondant "de la vue matérialisée" nous pouvons obtenir assez décent de performance, mais cela au détriment de compliquer les affaires de la logique, quelque chose que nous aimerions éviter, surtout depuis le A et le B tables sont déjà MV:s (et sont nécessaires pour les autres requêtes, et en fait les mêmes requêtes à l'aide d'un SYNDICAT).

create temporary table C engine=innodb as (select A.a, A.b, B.c from A join B on A.b = B.b);
explain select a, b, c from C where a = 44 order by c;

À compliquer davantage la situation est le fait que nous avons des conditions sur le B-table, tels que des filtres.

select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 AND B.c > 678 order by c;

Mais nous sommes confiants que nous pouvons gérer cela si le filesort problème disparaît.

Personne ne sait pourquoi le simple jointure dans codeblock 3 ci-dessus ne pas utiliser l'index pour le tri et si l'on peut contourner le problème en quelque sorte, sans la création d'un nouveau MV?

Ci-dessous la liste complète de SQL que l'on utilise pour les tests.

DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;
DROP TABLE IF EXISTS C;
CREATE TEMPORARY TABLE A (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b), INDEX idx_A_b (b)) ENGINE=INNODB;
CREATE TEMPORARY TABLE B (b INT NOT NULL, c INT NOT NULL, d VARCHAR(5000) NOT NULL DEFAULT '', PRIMARY KEY(b), INDEX idx_B_c (c), INDEX idx_B_b (b, c)) ENGINE=INNODB;
DELIMITER $$
CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
DECLARE _cnt INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
INSERT IGNORE INTO A SELECT RAND()*100, RAND()*10000;
INSERT IGNORE INTO B SELECT RAND()*10000, RAND()*1000, '';
SET _cnt = _cnt + 1;
END WHILE;
END
$$
DELIMITER ;
START TRANSACTION;
CALL prc_filler(100000);
COMMIT;
DROP PROCEDURE prc_filler;
CREATE TEMPORARY TABLE C ENGINE=INNODB AS (SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b);
ALTER TABLE C ADD (PRIMARY KEY(a, b), INDEX idx_C_a_c (a, c));
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE A.a = 44;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE 1 ORDER BY B.c;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b where A.a = 44 ORDER BY B.c;
EXPLAIN EXTENDED SELECT a, b, c FROM C WHERE a = 44 ORDER BY c;
-- Added after Quassnois comments
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM  B FORCE INDEX (idx_B_c) JOIN A ON A.b = B.b WHERE A.a = 44 ORDER BY B.c;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE A.a = 44 ORDER BY B.c LIMIT 10;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM  B FORCE INDEX (idx_B_c) JOIN A ON A.b = B.b WHERE A.a = 44 ORDER BY B.c LIMIT 10;

OriginalL'auteur Paso | 2009-08-04