À l'aide de l'index à l'intérieur de la table de jointure MySQL

J'ai de la table Foo avec 200 millions de disques et une table de Bar avec 1000 enregistrements, ils sont connectés plusieurs-à-un. Il y a des indices pour les colonnes Foo.parfois, et d'un Bar.someField. Également dans la Barre de 900 dossiers ont someField de 1, 100 ont someField de 2.

(1) Cette requête s'exécute immédiatement:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime     between '2008-08-14' and '2018-08-14' and b.someField = 1 limit 20;
...
20 rows in set (0.00 sec)

(2), celui-ci suffit pour toujours (le seul changement est b.someField = 2):

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where f.someTime     between '2008-08-14' and '2018-08-14' and b.someField = 2 limit 20;

(3), Mais si je baisse à la clause where sur parfois qu'il exécute aussi immédiatement:

mysql> select * from Foo f inner join Bar b on f.table_id = b.table_id where b.someField = 2 limit 20;
...
20 rows in set (0.00 sec)

(4) Aussi, je peux accélérer en forçant l'utilisation d'index:

mysql> select * from Foo f inner join Bar b force index(someField) on f.table_id = b.table_id where f.someTime     between '2008-08-14' and '2018-08-14' and b.someField = 2 limit 20;
...
20 rows in set (0.00 sec)

Voici l'expliquer sur requête (2) (qui prend une éternité)

+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| id | select_type | table | type   | possible_keys                 | key       | key_len | ref                      | rows     | Extra       |
+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | g     | range  | bar_id,bar_id_2,someTime      | someTime  | 4       | NULL                     | 95022220 | Using where |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY,someField,bar_id      | PRIMARY   | 4       | db.f.bar_id              |        1 | Using where |
+----+-------------+-------+--------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

Voici l'expliquer sur (4) (qui a l'indice de force)

+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
| id | select_type | table | type | possible_keys                 | key       | key_len | ref                      | rows     | Extra       |
+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+
|  1 | SIMPLE      | t     | ref  | someField                     | someField | 1       |   const                  |       92 |             |
|  1 | SIMPLE      | g     | ref  | bar_id,bar_id_2,someTime      | bar_id    | 4       | db.f.foo_id              | 10558024 | Using where |
+----+-------------+-------+------+-------------------------------+-----------+---------+--------------------------+----------+-------------+

Donc, la question est de savoir comment enseigner à MySQL d'utiliser le droit de l'index? La requête est générée par l'ORM et n'est pas limitée à ces deux champs. Et aussi ce serait bien d'éviter de changer la requête beaucoup (même si je ne suis pas sûr que la jointure interne correspond ici).

Mise à JOUR:

mysql> create index index_name on Foo (bar_id, someTime);

Après que la requête (2) s'exécute dans 0.00 sec.

Veuillez jamais ne SELECT * si vous SÉLECTIONNEZ a toutes les jointures. Au lieu de cela, spécifier les étoiles voulez-vous dire. Par exemple, SELECT f.* FROM foo f JOIN bar b ... est ok. Sinon, il n'est pas clair que les champs de votre * extrait, et il sera plus lent
J'ai utilisé SELECT * pour exemple, dans la vraie DB ORM génère la requête sans l' *.

OriginalL'auteur Yurii Shylov | 2013-06-13