J'ai réussi à mettre en place une requête qui fonctionne pour mes besoins, bien que plus compliquée que j'espérais. Mais, pour la taille des tables, la requête est plus lente qu'elle ne devrait être (0,17). La raison, basée sur le Query: strong> p> Ce particulier Requête, sélectionne les messages qui n'ont que la catégorie code> ordinateurs code>. Le but de Idéalement, je voudrais le faire fonctionner comme ceci: p> ou p> expliquer code> fourni ci-dessous, est que la table de numérisation de la table sur la table méta_relationhips code> en raison de la comptage code> dans le où code> clause sur un Innodb code> moteur. Nombre> 1 code> est d'exclure les messages contenant ordinateurs / matériel code>, ordinateurs / logiciels code>, etc., plus les catégories sélectionnées, Plus le nombre serait élevé. P> SELECT posts.post_id,posts.post_name,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
JOIN meta_relationships ON meta_relationships.object_id = posts.post_id
JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE posts.meta_count = 2
GROUP BY posts.post_id
HAVING category = 'category,subcategory'
5 Réponses :
Voir si cela vous donne la bonne réponse, éventuellement plus rapide:
SELECT p.post_id, p.post_name,
GROUP_CONCAT(IF(md.type = 'category', meta.meta_name, null)) AS category,
GROUP_CONCAT(IF(md.type = 'tag', meta.meta_name, null)) AS tag
FROM
( SELECT object_id
FROM meta_relation
GROUP BY object_id
HAVING count(*) = 1
) AS x
JOIN meta_relation AS mr ON mr.object_id = x.object_id
JOIN posts AS p ON p.post_id = mr.object_id
JOIN meta_data AS md ON mr.meta_data_id = md.meta_data_id
JOIN meta ON md.meta_id = meta.meta_id
WHERE meta.meta_name = ?
GROUP BY mr.object_id
Malheureusement, cette requête n'est que légèrement plus rapide que la mienne à 0,16. Il lit également toutes les lignes dans méta_relation .
Je ne peux pas penser à une façon de faire l'avoir sans lire toutes les rangées. Ou au moins toutes les lignes d'un indice, que vous avez semblé avoir, car elle a dit "en utilisant l'index".
Oh, j'ai une autre idée - mais cela dépend de où meta.meta_name =? être assez sélectif; est-ce?
où meta.meta_name =? peut contenir plusieurs catégories et balises.
Étant donné que l'ayant semble être le problème, pouvez-vous plutôt créer un champ de drapeau dans la table des poteaux et utiliser cela à la place? Si je comprends bien la requête correctement, vous essayez de trouver des messages avec un seul lien Meta_Relationhip. Si vous avez créé un champ dans la table de vos messages, ce qui était soit un nombre de méta_relayships pour ce poste, ou un drapeau booléen pour qu'il n'y en ait eu qu'un seul et indexé bien sûr, ce serait probablement beaucoup plus rapide. Cela impliquerait de mettre à jour le champ si le message a été modifié.
Ainsi, considérez ceci: p>
Ajoutez un nouveau champ à la table des poteaux appelé "num_meta_rel". Il peut s'agir d'un tinyint non signé tant que vous n'aurez jamais plus de 255 tags à n'importe quel message. P>
mettre à jour le champ comme celui-ci: p> Cette requête prendra du temps à courir, mais une fois que vous avez effectué tous les comptes préalculés. REMARQUE Ceci peut être mieux fait avec une jointure, mais SQLite (IDEONE) permet uniquement aux sous-sollicielles. P> MAINTENANT, vous réécrivez votre requête comme ceci: p> SELECT
posts.post_id,posts.post_name,
GROUP_CONCAT(IF(meta_data.type = 'category', meta.meta_name,null)) AS category,
GROUP_CONCAT(IF(meta_data.type = 'tag', meta.meta_name,null)) AS tag
FROM posts
RIGHT JOIN meta_relationships ON (posts.post_id = meta_relationships.object_id)
LEFT JOIN meta_data ON meta_relationships.meta_data_id = meta_data.meta_data_id
LEFT JOIN meta ON meta_data.meta_id = meta.meta_id
WHERE meta.meta_name = computers AND posts.num_meta_rel=1
GROUP BY meta_relationships.object_id
Ce n'est pas vraiment basé sur le nombre de catégories / tags, le compte me permet de sélectionner uniquement les messages avec ce niveau de catégorie correct. Par exemple, les ordinateurs de la catégorie ont des messages avec seulement la catégorie des ordinateurs, mais il possède également des messages avec des ordinateurs / matériels. Le comte filtre des postes contenant ces catégories supplémentaires. J'espère que cela à du sens.
Droit. Le problème est la sous-requête: Sélectionnez Meta_RelationShips.Object_id du groupe méta_relayships par meta_relationhips.Object_id comptez compter (*)> 1. Cela nécessite que toute la table (ou l'index) soit lu et regroupée, puis toutes les lignes avec> 1 comptoir être abandonné. Voir mon édition ci-dessus pour plus de détails.
Je suis content que vous compreniez le problème. Je ne suis pas excité d'avoir une autre colonne à mettre à jour, mais si cela fonctionne, cela peut être la voie à suivre. Meta_RelationsHIPS.META_ORE fonctionne effectivement de la même manière que vous proposez. Mais quand je fais où meta.meta_name = ordinateurs et méta_relation.coms.meta_order = 1 Il renvoie toujours chaque ligne avec la catégorie d'ordinateurs car il pourrait également y avoir un méta_order = 2. Idéalement c'est la façon dont je ' J'aime le faire fonctionner. Je ne peux pas tester votre solution pour le moment.
L'utilisation de Meta_Order ne fonctionnera que si vous souhaitez uniquement correspondre des lignes où "ordinateur" (ou quelle que soit la balise que vous recherchez) est toujours dans la première position. Dans ce cas, vous pouvez utiliser une jointure extérieure à la recherche de l'ID pour "ordinateur" en position 1 et null en position 2 (ce qui signifie qu'il n'y a pas une autre balise.
Ce sera toujours dans la première position, deuxième catégorie sera en deuxième position, etc.
J'étais vraiment excitée à propos de cette méthode, la requête exécutée en 0.06S! Mais j'ai découvert après avoir essayé de choisir plus d'une catégorie à la fois (animaux, oiseaux) qu'il n'a pas sélectionné de lignes.
Premièrement, je pense que les SMCJONES ont de bons points et c'est une solution moins que idéale. Mais, cela fonctionnera bien si vous recherchez deux catégories - assurez-vous simplement de changer le message.num_meta_rel = 1 à post.MUM_MEA_REL = 2. En tout état de cause, une optimisation supplémentaire comme ci-dessus est probablement la voie à suivre.
Je l'ai changé à 2. Mais malheureusement, je pense qu'il y a un problème beaucoup plus gros ici. Ma requête originale de la question ne fonctionne plus correctement et a également le même problème de pouvoir sélectionner une catégorie. Une fois que je suis sorti cela, je pense que l'une de ces réponses pourrait réellement être ce dont j'ai besoin.
Avec une combinaison de requêtes optimisées strong> em> l'optimisation de vos tables, vous aurez des requêtes rapides. Cependant, vous ne pouvez pas avoir des requêtes rapides sans table optimisée Je ne peux insister assez sur ce point. Si vos tables sont correctement structurées avec la bonne quantité d'indices, vous ne devriez pas être confronté à une table complète se lit sur une requête comme GROUP BY ... HAVING à moins que vous le faites par la conception. strong> p> Selon votre exemple, j'ai créé cette SQLFiddle. p> Comparer que SQLFiddle # 2 , dans lequel j'ai ajouté des index et ajouté un de mes tests, Fiddle # 2 est plus rapide. p> cette requête me conduisait les noix, même après avoir fait l'argument selon lequel les indices seraient la meilleure façon d'optimiser cela. Même si je tiens encore que la table est votre plus grande opportunité de d'augmenter les performances, il ne semble qu'il devait y avoir une meilleure façon d'exécuter cette requête dans MySQL. J'ai eu une révélation après avoir dormi sur ce problème, et utilisé la requête suivante (voir dans SQLFiddle # 3 ): p> UNIQUE code> index contre meta.meta_naame code>. p> optimisation de votre requête h1>
CREATE TABLE meta_data (
meta_data_id BIGINT,
meta_id BIGINT,
type VARCHAR(50),
description VARCHAR(200),
parent BIGINT,
count BIGINT,
PRIMARY KEY (meta_data_id,meta_id),
INDEX ix_meta_id (meta_id)
);
Bien que je suis d'accord avec vous que les index doivent être améliorés, la base de données est suffisamment petite pour ne pas avoir d'impact important. Je crois que la clé d'amélioration de manière significative la vitesse est d'optimiser la requête elle-même et d'empêcher la numérisation de la table.
@Eternalhour Vous n'en savez pas assez pour faire des déclarations comme ça. Les indices (index des yanks) sont ce qui empêche les analyses de la table, quelle que soit la "optimisation".
@DavidSoussan - J'ai tendance à être d'accord avec vous, car les "indices" sont pris en compte par l'optimiseur pour effectuer la requête. Mais peu importe ce que vous pensez que je sais, j'ai déjà des indices en place (comme suggéré) et je ne crois toujours pas que c'est ce qui cause la question de la performance.
Je ne suis pas sûr que votre SQL est correct alors, car si vous avez essayé de mettre une clé primaire sur meta_data.meta_data_id , alors cela échouerait parce que trois rangées partagent "ID 10". Mise à jour de ma réponse pour refléter des choses comme celle-ci.
Vos index ne sont pas optimisés si vous avez suivi mes instructions au TEE dans la première course. Vous avez besoin d'une contrainte unique contre meta.meta_name . Sinon, la table fera inévitablement une numérisation de table complète lorsque vous recherchez des "animaux" ou "divertissement" ou "ordinateurs" ou quoi que ce soit d'autre.
J'ai fait quelques modifications finales. S'il vous plaît laissez-moi savoir comment cela fonctionne.
C'est une bonne réponse @smcjones, le teste actuellement. J'ai perdu le sommeil sur ce problème :)
Il est légèrement plus rapide après avoir modifié un peu les index. Mais je ne peux pas sembler trouver un moyen de sélectionner les enregistrements de manière hiérarchique de cette façon. Par exemple, il sélectionne chaque enregistrement qui correspond aux animaux , mais il se peut que vous ne puissiez choisir que les lignes animaux non associées à une seconde catégorie.
Vos exemples ne montrent pas vraiment ce cas d'utilisation, ou du moins, j'ai eu du mal à le voir. Par "catégorie" Voulez-vous dire ce qui se trouve sous la table Meta_Data et comparé à l'aide de relations ? Je suppose que c'est ce que vous voulez dire et non la colonne parent . Le groupe par ... ayant (item_relation.coms.object_id) = 1 devrait limiter à un seul type.
Les catégories sont conservées dans la table méta . Le tableau META_DATA LIENS LIENS LES CATÉGORIES À META_RelAVESHIPS et META_RelationsHIPS Liens Les messages sur META_DATA et chaque catégorie. J'ai une sorte de perte d'espoir que je vais toujours améliorer la performance. Je peux commencer à essayer de trouver un schéma différent à la place.
Si cela est possible, ce serait une bonne idée. Vos tables ne sont pas tout à fait 3nf et s'ils étaient vos questions seraient beaucoup plus simples. En tout état de cause, vous devriez être capable de grouper par catégorie et métadonnées, puis utiliser la syntaxe pour restreindre l'ID de relation.
Malheureusement, je n'ai aucune possibilité de tester les performances,
mais essayez ma requête en utilisant vos données réelles:
Merci pour la réponse Alex. Cette requête est en fait plus rapide @ 0.11, mais il y a encore une table numérisée :(
Pourriez-vous fournir plus de données pour le violon et le débogage?
Bien sûr, quelles données avez-vous besoin? Dans le violon, vous pouvez voir qu'il y a 18 lignes pour méta_relationships . Si vous faites un expliquer il affiche 18 lignes ont été lus (numérisation de table), c'est ce que j'essaie d'éviter. La requête renvoie 2 lignes alors j'espère voir 2 lignes pour méta_relationships dans le expliquer .
J'ai juste besoin de plus d'échantillons de données pour voir les améliorations de la vitesse si
Eh bien, il y a 15 000 rangées dans cette table, je ne pourrai pas les ajouter dans le violon. Si vous pouvez éviter la numérisation de la table, les performances s'amélioreront automatiquement.
J'ai également ajouté plus de détails à la question, j'espère que cela aide.
Utilisez au lieu de
Cela a le même résultat mais est un peu plus lent.
Pouvez-vous fournir
show créer une table de table pour chaque table, en particulierméta_relation , afin que nous puissions voir quels indices sont composés.Expliquer (en anglais) le but du
pas dans ; C'est là que l'analyse de la table est. (Et vous avez de la chance - dans les anciennes versions, il fonctionnerait beaucoup plus lent.)@RickJames - Le but est d'éliminer tout
objet_id qui possède plus d'une catégorie ou une balise.C'est un peu
méta pour moi. Vous voulez sélectionner des messages qui ont au maximum une balise?@LEGEC - sur l'exemple requête Oui, mais je dois pouvoir sélectionner n'importe quel nombre de catégories.