Left join est ce que je veux, mais ils sont très lents?

Vue d'ensemble:

J'ai trois tables 1) les abonnés, bios, et shirtsizes et j'ai besoin de trouver les abonnés sans un bio ou shirtsizes

les tables sont disposées comme

abonnés

| season_id |  user_id |

bio

| bio_id | user_id |

shirt tailles

| bio_id | shirtsize |

Et j'ai besoin de trouver tous les utilisateurs qui n'ont pas de bio ou shirtsize, (si pas bio; alors pas de shirtsize par rapport) pour une saison donnée.

Départ, j'avais écrit une requête comme:

SELECT *
   FROM subscribers s 
   LEFT JOIN bio b ON b.user_id = subscribers.user_id 
   LEFT JOIN shirtsizes ON shirtsize.bio_id = bio.bio_id 
WHERE s.season_id = 185181 AND (bio.bio_id IS NULL OR shirtsize.size IS NULL);

mais il prend 10 secondes maintenant.

Je me demande comment je peut restructurer la requête (ou peut-être le problème) afin qu'il procède de façon raisonnable.

Voici le mysql expliquer: (ogu = abonnés, b = bio, tn = shirtshize)

| id | select_type | table | type  | possible_keys | key     | key_len | ref         | rows   | Extra       |   
+----+-------------+-------+-------+---------------+---------+---------+-------------+--------+-------------+    
|  1 | SIMPLE      | ogu   | ref   | PRIMARY       | PRIMARY | 4       | const       |    133 | Using where |
|  1 | SIMPLE      | b     | index | NULL          | PRIMARY | 8       | NULL        | 187644 | Using index |
|  1 | SIMPLE      | tn    | ref   | nid           | nid     | 4       | waka2.b.nid |      1 | Using where |

Ci-dessus est assez aseptisée, voici la realz info:

mysql> DESCRIBE subscribers
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| subscribers  | int(11) | NO   | PRI |         |       | 
| uid       | int(11) | NO   | PRI |         |       | 


mysql> DESCRIBE bio;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bio_id   | int(10) unsigned | NO   | PRI | 0       |       | 
| uid   | int(10) unsigned | NO   | PRI | 0       |       | 


mysql> DESCRIBE shirtsize;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| bio_id   | int(10) unsigned | NO   | PRI | 0       |       | 
| shirtsize   | int(10) unsigned | NO   | PRI | 0       |       |

et le réel de requête ressemble:

SELECT ogu.nid, ogu.is_active, ogu.uid, b.nid AS bio_node, tn.nid AS size
                  FROM og_uid ogu
                  LEFT JOIN bio b ON b.uid = ogu.uid
                  LEFT JOIN term_node tn ON tn.nid = b.nid
                  WHERE ogu.nid = 185033 AND ogu.is_admin = 0
                  AND (b.nid IS NULL OR tn.tid IS NULL)

nid est season_id ou bio_id (avec un type);
term_node va être le shirtsize

Ces tables ont des index?
Pouvons-nous vous demandons d'inclure "SHOW CREATE TABLE" de sortie pour chaque table?

OriginalL'auteur jskulski | 2009-03-10

14

La requête devrait être OK. Je voudrais le lancer à travers un analyseur de requêtes et d'affiner les index sur les tables.

Je suppose que c'est ce que je reçois pour la confiance des modules contribués à corriger les index. Merci!

OriginalL'auteur Tor Haugen
12

Jointures sont l'un des plus chers des opérations que vous pouvez effectuer sur une requête SQL. Alors qu'il devrait être en mesure d'optimiser automatiquement votre requête un peu, peut-être essayer de les restructurer. Tout d'abord, je voudrais, au lieu de SÉLECTIONNER *, assurez-vous de spécifier les colonnes dont vous avez besoin à partir de laquelle les relations. Cela permettra d'accélérer les choses un peu.

Si vous avez seulement besoin de l'ID utilisateur par exemple:
```
SELECT s.user_id
   FROM subscribers s 
   LEFT JOIN bio b ON b.user_id = subscribers.user_id 
   LEFT JOIN shirtsizes ON shirtsize.bio_id = bio.bio_id 
WHERE s.season_id = 185181 AND (bio.bio_id IS NULL OR shirtsize.size IS NULL);
```
Qui va permettre à la base de données SQL pour restructurer votre requête un peu plus efficace sur son propre.

Régulièrement les jointures sont modérément coûteux; les jointures externes sont plus chers, typique de la sous-requête corrélée est diabolique.
J'ai posté le réel de la requête et ne fais que profiter de quelques colonnes, mais merci.

OriginalL'auteur Brian
4

Évidemment, je n'ai pas vérifié, mais il me semble que ce que vous voulez est de sélectionner tout abonné, là où il n'y a pas de correspondance bio ou de la jointure entre le bios et le shirtsizes échoue. Je voudrais envisager d'utiliser N'EXISTE PAS pour cette condition. Vous aurez probablement envie d'indices sur le bio.user_id et shirtsizes.bio_id.
```
select *
from subscribers
where s.season_id = 185181
      and not exists (select *
                      from bio join shirtsizes on bio.bio_id = shirtsizes.bio_id
                      where bio.user_id = subscribers.user_id)
```
MODIFIER:

Basé sur votre mise à jour, vous pouvez créer des clés séparées sur chaque colonne au lieu de/en plus d'avoir composé des clés primaires. Il est possible que les jointures ne sont pas en mesure de prendre avantage optimal du composé primaire index et d'un index sur les colonnes de jointure se peut accélérer les choses.

Très peu de gens ont étudié cette question et il s'avère que SELECT * est normalement légèrement plus rapide que le SELECT 1 / SELECT NULL / etc dans un EXISTE requête. Mais oui, à l'aide EXISTE peut souvent être plus rapide que de se joindre, mais pas toujours, c'est une arme à essayer dans chaque scénario...
Point de pris. J'ai mis à jour la réponse.

OriginalL'auteur tvanfosson
1

Est bio_id la clé primaire de bios? Est-il vraiment possible d'avoir un bios de ligne avec b.user_id = subscribers.user_id mais avec b.bio_id NULL?

Sont là shirtsize lignes avec shirtsize.bio_id NULL? Ces lignes ont jamais shirtsize.taille non NULLE?

OriginalL'auteur John Saunders
1

Serait-il plus rapide de faire une différence entre la liste des abonnés de la saison et de la liste des abonnés pour la saison avec le bios et les tailles?
```
SELECT *
   FROM Subscribers
   WHERE season_id = 185181
     AND user_id NOT IN
         (SELECT DISTINCT s.user_id
             FROM subscribers s
             JOIN bios b ON s.user_id = b.user_id
             JOIN shirtsizes z ON b.bio_id = z.bio_id
             WHERE s.season_id = 185181
         )
```
Cela évite les jointures externes, qui ne sont pas aussi rapide que les jointures internes, et peuvent donc être plus rapide. D'autre part, il pourrait être la création de deux grandes listes avec très peu de différences entre eux. Il n'est pas clair si l'DISTINCTES dans la sous-requête d'améliorer ou nuire à la performance. Il implique une opération de tri (cher), mais ouvre la voie à une fusion-joignez-vous si l'optimiseur MySQL prend en charge de telles choses.

Il y a peut être d'autres notations disponibles - MINUS ou la DIFFÉRENCE, par exemple.

Il n'y a pas de raison d'utiliser DISTINCTES dans la sous-requête.

OriginalL'auteur Jonathan Leffler
1

Si vous définir ce que vous recherchez exactement, plutôt que de SÉLECTIONNER * il se peut accélérer un peu... OU n'est pas la manière la plus rapide requête à faire, si vous pouvez ré-écrire sans la OU il sera plus rapide.

Aussi... vous pouvez essayer les syndicats au lieu de gauche rejoint peut-être?
```
SÉLECTIONNEZ s.user_id 
DE la part des abonnés s 
LEFT JOIN bio b SUR b.user_id = s.user_id 
LEFT JOIN shirtsizes SUR shirtsize.bio_id = bio.bio_id 
OÙ s.season_id = 185181 ET bio.bio_id EST NULL OU shirtsize.la taille EST NULLE); 
```
serait quelque chose comme:
```
(SÉLECTIONNEZ s.user_id D'abonnés s OÙ s.season_id = 185181) 
UNION 
(SÉLECTIONNEZ b.user_id, b.bio_id DE bio b OÙ bio.bio_id EST NUL) 
UNION 
(SÉLECTIONNEZ shirtsizes.bio_id DE shirtsizes OÙ shirtsizes.la taille est NULLE) 
```
(pour être honnête, qui n'est pas bon pour moi... mais je n'ai jamais utiliser ~~joint or~~ la syntaxe de jointure ou de syndicats...)

Je ferais:
```
SELECT * 
DE la part des abonnés s, bio b, shirtsizes sh 
OÙ s.season_id = 185181 
ET shirtsize.bio_id = bio.bio_id 
ET b.user_id = s.user_id 
ET (bio).bio_id EST NULL 
OU 
shirtsize.la taille EST NULLE); 
```
"Je n'ai jamais utiliser les jointures ou les syndicats" bien sûr, vous ne utilisez des jointures. DE la part des abonnés s, bio b, shirtsizes sh OÙ s.season_id = 185181 ET shirtsize.bio_id = bio.bio_id ET b.user_id = s.user_id est un ensemble de jointures. C'est à l'aide de l'ancienne style de jointure, mais il est encore une jointure.
Je pense qu'il aurait été plus correct d'écrire " je ne peux pas utiliser la syntaxe de jointure et je n'ai jamais utiliser des syndicats

OriginalL'auteur SeanJA

Votre requête, comme il est écrit maintenant, évalue toutes les bio's et term_node's, si elles existent, et les filtres.

Mais ce que vous voulez, c'est juste trouver og_uid's qui n'ont pas term_node's (ne pas avoir un bio implique aussi de ne pas avoir un term_node)

Donc, vous voulez arrêter de l'évaluation bio's et term_node'dès que vous trouvez le premier existant term_node:

SELECT  *
FROM    (
        SELECT  ogu.nid, ogu.is_active, ogu.uid,
                (
                SELECT  1
                FROM    bio b, term_node tn
                WHERE   b.uid = ogu.uid
                        AND tn.nid = b.nid
                LIMIT   1
                ) AS ex
        FROM    og_uid ogu
        WHERE   ogu.nid = 185033
                AND ogu.is_admin = 0
        ) ogu1
WHERE   ex IS NULL

Cela permettra d'évaluer au plus une bio et au plus une term_node pour chaque og_uid, au lieu d'évaluer tous les milliers et le filtrage.

Doit travailler beaucoup plus vite.

OriginalL'auteur Quassnoi

select * from subscribers where user_id not in (
  select user_id from bio where bio_id not in (
    select bio_id from shirt_sizes
  )
) and season_id=185181

OriginalL'auteur Hafthor

0

Je présume que votre "big table" est aux abonnés, et que season_id est probablement ni sélective ni indexé (indexation, il est plutôt vide de sens si elle n'est pas sélective, de toute façon), ce qui signifie que vous devrez entièrement numériser des abonnés, de toute façon. Adieu, je voudrais rejoindre (avec une jointure interne) les deux autres tables de noter que si il n'y a pas de bio_id dans shirt_size c'est exactement la même chose pour votre requête, comme si il n'y avait pas bio.
Premier bit:
```
select uid
from bio
     inner join shirtsizes
             on shirtsizes.bio_id = bio.bio_id
```
À quel point vous voulez vous assurer que shirtsizes est indexé sur bio_id.
Maintenant, vous pouvez jointure externe gauche de cette requête pour les abonnés:
```
select *
from subscribers s
     left outer join (select uid
                      from bio
                      inner join shirtsizes
                              on shirtsizes.bio_id = bio.bio_id) x
                  on x.uid = s.uid
where s.season_id = 185181
  and x.uid is null
```
qui est susceptible de s'exécuter raisonnablement rapide si ni bio, ni shirtsizes sont gigantesques ...

OriginalL'auteur

Vous devez vous connecter pour publier un commentaire.