Quelle est la méthode la plus efficace pour récupérer les premiers, derniers et 3 enregistrements du milieu en MySQL?

Question

Contexte

J'ai une page web qui affiche des bandes dessinées en ligne. Il y a actuellement 1622 pages. J'ai besoin d'afficher la page de bande dessinée actuelle (j'ai son ID), ainsi que les liens vers les premières, précédentes, suivantes et dernières pages. L'ordre est également complexe (il y a un long ORDER BY en raison de la conception de base de données particulière - des éléments hérités), donc je ne peux pas faire des choses comme "où ID = 1" non plus.

La question

Donc, la question est simple - je connais l'ID d'un enregistrement. Je fais une requête SELECT ... FROM ... WHERE ... ORDER BY ... et je veux récupérer le premier enregistrement, le dernier enregistrement, l'enregistrement avec l'ID que je connais, et les enregistrements précédents et suivants celui avec l'ID connu.

La requête non filtrée renvoie plus de 1600 lignes, et il y a une nouvelle ligne chaque jour. La requête sera exécutée plusieurs fois par seconde (il y a un lectorat important). Quelle est la manière la plus efficace de procéder ? Y a-t-il quelque chose de mieux que la méthode naïve consistant à "obtenir toutes les lignes et filtrer ce dont j'ai besoin en code PHP" ? Notez que je sais que je peux mettre en cache le résultat côté PHP, mais je me demandais s'il y a une optimisation liée à MySQL disponible ici.

Ajout : Une solution consiste à faire plusieurs requêtes - une pour chaque valeur requise. Je devrais l'avoir mentionné et je pensais à quelque chose de plus élégant.

Answer 1

Pourquoi ne pas ajouter une colonne OrderedID qui est séquentielle et ordonnée de la bonne manière ? Vous pourriez la remplir initialement en utilisant votre requête complexe, puis la mettre à jour au fur et à mesure que de nouvelles pages sont ajoutées.

Si modifier la table existante n'est pas une option, vous pourriez créer une nouvelle table avec seulement deux colonnes, une FK qui pointe vers votre table de pages, et une colonne OrderedID comme ci-dessus ?

Cela signifie que pour toute page ID=X, vous auriez besoin de 1, X-1, X, X+1, et Max() - seul Max() nécessite réellement une requête, et cela ne changerait qu'une fois par jour. Les autres pourraient simplement être calculés.

Answer 2

Eh bien.. Je pense que ce problème peut être résolu avec beaucoup moins de logique. Si les ID sont incrémentés automatiquement (ce qui rendrait cela super simple), vous pouvez vraiment utiliser des opérations arithmétiques de base.

SÉLECTIONNER count(comicId)
DE bandes dessinées;

// Obtenez cette réponse en php
$low = 0; // cela pourrait être n'importe quoi. 
$high = count;
$one = (($high - $low) / 2) + ($low - 1);
$two = $one + 1;
$three = $two + 1;

SÉLECTIONNER *
DE bandes dessinées
OÙ comicId IN ($low, $high, $one, $two, $three);

---

La raison pour laquelle $low devrait être quelque chose, c'est que vous pourriez mettre à jour bas ici et là pour que la bande dessinée "ancienne" soit... eh bien.. moins ancienne (si cela a du sens). Donc au lieu de commencer à 0 (première bande dessinée jamais), vous pourriez commencer par 50, 100, 1000, etc. :)

---

---

D'accord, maintenant puisqu'il y a cette information (que tous les ID n'existent pas (0-1600) et certains d'entre eux ne sont pas pertinents).

Rappelez-vous que ce n'est pas une solution finale, je suis en train d'en inventer une. Il y a plein d'options à prendre en compte lors de la programmation. Gardez à l'esprit les efficacités (SI NÉCESSAIRE).

1: Créez une sorte de table de lien, appelez-la comme vous voulez, [bandesdessinéespertinentes] avec 2 champs, l'ID des bandes dessinées désirées et un champ d'incrémentation automatique.

2: FAITES quelque peu la même logique que ci-dessus mais modifiez en conséquence.

$low = 0; // cela pourrait être n'importe quoi. 
$high = count;
$one = (($high - $low) / 2) + ($low - 1);
$two = $one + 1;
$three = $two + 1;

SÉLECTIONNER C.*
DE bandes dessinées COMME C
    REJOINDRE bandesdessinéespertinentes COMME RC
      SUR RC.id = C.comicid
OÙ RC.autoId IN ($low, $high, $one, $two, $three);

Tant que les bandes dessinées sont insérées dans un ordre approprié, cela devrait fonctionner pour vous! Ce qui fonctionne, c'est que le champ automatique est alors simplement placé dans une table séparée, puis récupéré et joint à partir de là. De cette façon, vos données existantes n'ont pas à être modifiées, sauf lors de l'insertion de nouvelles bandes dessinées, la table pertinente doit également être mise à jour.

Answer 3

Avec seulement 1600 lignes, je ne pense pas qu'il y ait le moindre problème d'efficacité, quelle que soit la manière dont vous implémentez cela. Supposons cependant que vous ayez 16 millions de lignes.

En supposant que votre requête ressemble à ceci :

SELECT ...  FROM ...  WHERE ... 
ORDER BY colA ASC
       , colB DESC
       , ...
       , colZ ASC

et que id est une clé unique et que l'identifiant spécifique est @id.

Vous pouvez ajouter un index sur (colA, colB, ..., colZ) et essayer ceci :

  ( SELECT ...  FROM ...  WHERE ... 
    ORDER BY colA ASC
           , colB DESC
           , ...
           , colZ ASC
    LIMIT 1          --- pour obtenir la première ligne
  )
  UNION ALL
  ( SELECT ...  FROM ...  WHERE ... 
                            AND (colA, colB, ..., colZ) 
          <  ( SELECT colA, colB, ..., colZ
               FROM ... 
               WHERE id = @id )
    ORDER BY colA DESC       --- ordre inversé
           , colB ASC        --- ordre inversé
           , ...
           , colZ DESC       --- ordre inversé
    LIMIT 1          --- pour obtenir la ligne précédente
  )
  UNION ALL
  ( SELECT ...  FROM ...  WHERE ... 
                            AND (colA, colB, ..., colZ) 
          >= ( SELECT colA, colB, ..., colZ
               FROM ... 
               WHERE id = @id )
    ORDER BY colA ASC
           , colB DESC
           , ...
           , colZ ASC
    LIMIT 2          --- pour obtenir la ligne avec @id et la suivante
  )
  UNION ALL
  ( SELECT ...  FROM ...  WHERE ... 
    ORDER BY colA DESC       --- ordre inversé
           , colB ASC        --- ordre inversé
           , ...             --- ...
           , colZ DESC       --- ordre inversé
    LIMIT 1          --- pour obtenir la dernière ligne
  )