Comment puis-je optimiser davantage une requête de table dérivée qui fonctionne mieux que l'équivalent joint?

Eh bien, j'ai trouvé une solution. Il a fallu beaucoup d'expérimentation, et je pense qu'un bon peu de chance aveugle, mais le voici:

CREATE TABLE magic ENGINE=MEMORY
SELECT
  s.shop_id AS shop_id,
  s.id AS shift_id,
  st.dow AS dow,
  st.start AS start,
  st.end AS end,
  su.user_id AS manager_id
FROM shifts s
JOIN shift_times st ON s.id = st.shift_id
JOIN shifts_users su ON s.id = su.shift_id
JOIN shift_positions sp ON su.shift_position_id = sp.id AND sp.level = 1

ALTER TABLE magic ADD INDEX (shop_id, dow);

CREATE TABLE tickets_extra ENGINE=MyISAM
SELECT 
  t.id AS ticket_id,
  (
    SELECT m.manager_id
    FROM magic m
    WHERE DAYOFWEEK(t.created) = m.dow
    AND TIME(t.created) BETWEEN m.start AND m.end
    AND m.shop_id = t.shop_id
  ) AS manager_created,
  (
    SELECT m.manager_id
    FROM magic m
    WHERE DAYOFWEEK(t.resolved) = m.dow
    AND TIME(t.resolved) BETWEEN m.start AND m.end
    AND m.shop_id = t.shop_id
  ) AS manager_resolved
FROM tickets t;
DROP TABLE magic;

Longue Explication

Maintenant, je vais vous expliquer pourquoi cela fonctionne, et mon processus relatif et les étapes pour arriver ici.

Tout d'abord, je savais que la requête que j'essayais souffrait à cause de l'énorme table dérivée, et les jointures ultérieures sur ceci. Je prenais ma table de tickets bien indexée et je joignais toutes les données shift_times, puis je laissais MySQL mâche cela pendant qu'il tente de rejoindre la table shifts et shift_positions. Ce mastodonte dérivé serait jusqu'à un désordre non indexé de 2 millions de lignes.

Maintenant, je savais que cela se produisait. La raison pour laquelle j'allais dans cette voie était que la façon "correcte" de le faire, en utilisant strictement les jointures, prenait encore plus de temps. Cela est dû au peu méchant de chaos nécessaire pour déterminer qui est le gestionnaire d'un quart de travail donné. Je dois rejoindre shift_times pour savoir ce que le le décalage correct est même, tout en se joignant simultanément à shift_positions pour comprendre le niveau de l'utilisateur. Je ne pense pas que L'optimiseur MySQL gère très bien cela, et finit par créer une énorme monstruosité d'une table temporaire des jointures, puis filtrer ce qui ne s'applique pas.

Donc, comme la table dérivée semblait être la "voie à suivre", j'ai obstinément persisté pendant un moment. J'ai essayé de le lancer dans une clause de jointure, aucune amélioration. J'ai essayé de créer une table temporaire avec le table dérivée, mais encore une fois c'était trop lent car la table temporaire n'était pas indexée.

Je me suis rendu compte que je devais gérer ce calcul de décalage, temps, positions sanely. Je pensais, peut-être une vue serait la voie à suivre. Que faire si j'ai créé une vue contenant ces informations: (shop_id, shift_id, dow, start, end, manager_id). Ensuite, je devrais simplement rejoindre la table des tickets par shop_id et tout le calcul DAYOFWEEK/TIME, et je serais en affaires. Bien sûr, je n'ai pas réussi à rappelez-vous que MySQL gère les vues plutôt assily. Il ne les matérialise pas du tout, il exécute simplement la requête que vous auriez utilisée pour obtenir la vue pour vous. Donc, en joignant des tickets à cela, j'exécutais essentiellement ma requête originale-aucune amélioration.

Donc, au lieu d'une vue, j'ai décidé d'utiliser une TABLE temporaire. Cela a bien fonctionné si Je ne récupérais qu'un des gestionnaires (créés ou résolus) à la fois, mais c'était encore assez lent. En outre, j'ai découvert qu'avec MySQL vous ne pouvez pas vous référer à la même chose table deux fois dans la même requête (je devrais rejoindre ma table temporaire deux fois pour pouvoir différencier manager_created et manager_resolved). C'est un grand WTF, car je peux le faire tant que je ne spécifie pas "temporaire" - c'est là que le CREATE TABLE Magic ENGINE=MEMORY est entré en jeu.

Avec cette table pseudo temporaire en main, j'ai essayé ma JOIN for just manager_created à nouveau. Il a bien fonctionné, mais encore assez lent. Pourtant, quand je me suis joint à nouveau pour obtenir manager_resolved dans le même requête le temps de requête coché dans la stratosphère. En regardant L'explication a montré l'analyse complète de la table des tickets (lignes ~2mln), comme prévu, et les jointures sur la table magique à ~2,087 chacun. Encore une fois, je semblais courir en échec.

J'ai maintenant commencé à réfléchir à la façon d'éviter complètement les jointures et c'est à ce moment-là que j'ai trouvé un ancien message obscur où quelqu'un a suggéré d'utiliser des sous-sélections (Je ne trouve pas le lien dans mon histoire). C'est ce qui a conduit à la deuxième requête SELECT montré ci-dessus (la création tickets_extra un). Dans le cas de la sélection d'un seul champ de gestionnaire, il a bien fonctionné, mais encore une fois avec les deux c'était de la merde. J'ai regardé L'explication et j'ai vu ceci:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 173825
        Extra: 
*************************** 2. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2037
        Extra: Using where
*************************** 3. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: m
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2037
        Extra: Using where
3 rows in set (0.00 sec)

Ack, la sous-requête dépendante redoutée. Il est souvent suggéré de les éviter, car MySQL Les exécutera généralement de manière externe, en exécutant la requête interne pour chaque ligne de l'externe. J'ai ignoré cela, et je me suis demandé: "Eh bien... et si j'indexais cette stupide table magique?". Ainsi, l' Ajouter index (shop_id, dow) est né.

Vérifiez ceci:

mysql> CREATE TABLE magic ENGINE=MEMORY
<snip>
Query OK, 3220 rows affected (0.40 sec)

mysql> ALTER TABLE magic ADD INDEX (shop_id, dow);
Query OK, 3220 rows affected (0.02 sec)

mysql> CREATE TABLE tickets_extra ENGINE=MyISAM
<snip>
Query OK, 1933769 rows affected (24.18 sec)

mysql> drop table magic;
Query OK, 0 rows affected (0.00 sec)

Maintenant C'est de quoi je parle!

Conclusion

C'est certainement la première fois que j'ai créé une table non temporaire à la volée, et que je L'ai indexée à la volée, simplement pour faire une seule requête efficacement. Je suppose que j'ai toujours supposé que l'ajout d'un index à la volée est une opération prohibitive. (L'ajout d'un index sur ma table de tickets de lignes 2mln peut prendre plus d'une heure). Pourtant, pour une simple 3 000 lignes c'est une promenade.

N'ayez pas peur des sous-requêtes dépendantes, de la création de tables temporaires qui ne le sont vraiment pas, de l'indexation à la volée ou des extraterrestres. Ils peuvent tous être de bonnes choses dans la bonne situation.

Merci pour toute L'aide StackOverflow. :- D