MySQL pagination sans double interrogation?

Je me demandais si il y avait un moyen pour obtenir le nombre de résultats pour une requête MySQL, et en même temps de limiter les résultats.

La façon dont la pagination fonctionne (ce que je comprends), j'ai d'abord faire quelque chose comme

query = SELECT COUNT(*) FROM `table` WHERE `some_condition`

Après je reçois le num_rows(requête), j'ai le nombre de résultats. Mais de là à en fait la limite de mes résultats, je dois faire une deuxième requête comme:

query2 = SELECT COUNT(*) FROM `table` WHERE `some_condition` LIMIT 0, 10

Ma question: Est-il de toute façon à la fois de récupérer le nombre total de résultats qui serait donné, ET de limiter les résultats renvoyés en une seule requête? Ou de toute façon la plus efficace de le faire. Merci!

Bien que vous ne l'auriez pas COUNT(*) dans query2

InformationsquelleAutor atp | 2009-05-04

60

Pas, c'est combien les applications qui veulent paginer le faire. Il est fiable et à l'épreuve des balles, mais il fait la requête à deux reprises. Mais vous pouvez mettre en cache le comte de quelques secondes et ça va nous aider beaucoup.

L'autre façon est d'utiliser des SQL_CALC_FOUND_ROWS clause et ensuite appeler SELECT FOUND_ROWS(). outre le fait que vous devez mettre la FOUND_ROWS() appel par la suite, il y a un problème avec cela: Il y a un bug dans MySQL que ce chatouille qui affecte ORDER BY requêtes rendant beaucoup plus lent sur les grandes tables de l'approche naïve de deux requêtes.
- Excellent, merci pour votre aide!
- Ce n'est pas tout à fait de la course de la condition de la preuve, cependant, à moins que vous ne les deux requêtes au sein d'une transaction. Ce n'est généralement pas un problème, cependant.
- Par "fiable" je voulais dire le SQL lui-même est toujours retourner le résultat que vous souhaitez, et par "pare-balles", je voulais dire qu'il n'y a pas de MySQL bugs qui entravent ce que SQL, vous pouvez utiliser. Contrairement à l'utilisation de SQL_CALC_FOUND_ROWS avec ORDER BY et LIMIT, selon le bug que j'ai mentionnés.
- +1. N'avais pas entendu parler du bug avant
- Sur des requêtes complexes, à l'aide de SQL_CALC_FOUND_ROWS pour aller chercher le comte dans la même requête sera presque toujours plus lent que de faire deux requêtes distinctes. C'est parce qu'il signifie que toutes les lignes doivent être récupérées en totalité, indépendamment de la limite, alors seulement ceux qui sont spécifiés dans la clause LIMIT sont retournés. Voir aussi ma réponse qui a des liens.
- Selon la raison pour laquelle vous avez besoin de cela, vous pouvez aussi envie de penser à tout simplement pas de récupérer le total des résultats. Il est de plus en plus courant de mettre en œuvre l'auto-pagination méthodes. Des Sites comme Facebook, Twitter, Bing et Google ont eu recours à cette méthode pour les âges.
InformationsquelleAutor staticsan
64

Je n'ai presque jamais faire deux requêtes.

Il suffit de retourner un plus de lignes que cela est nécessaire, seulement l'affichage 10 sur la page, et si il y a plus que ce qui est affiché, l'affichage d'un bouton "Suivant".
```
SELECT x, y, z FROM `table` WHERE `some_condition` LIMIT 0, 11
```
```
//iterate through and display 10 rows.

//if there were 11 rows, display a "Next" button.
```
Votre requête doit retourner dans un ordre des plus pertinents en premier. Les Chances sont, la plupart des gens ne vont pas soin d'aller à la page 236 de 412.

Lorsque vous faites une recherche sur google, et vos résultats ne sont pas sur la première page, vous aurez probablement aller à la page deux, pas neuf.
- C'est vrai, je vais garder cela à l'esprit.
- En fait, si je ne le trouve pas sur la première page de Google sur la requête, j'ai l'habitude de faire passer à la page neuf.
- J'ai entendu cela avant, mais pourquoi faire?
- Un peu en retard, mais voici mon raisonnement. Certaines recherches sont dominés par le moteur de recherche optimisé des fermes de liens. Si les premières pages sont les différentes fermes de combattre pour la position numéro 1, le résultat utile est probablement associée à la requête, tout simplement pas sur le dessus.
- COUNT est une fonction d'agrégation. Comment pouvez vous retourner le comte et tous les résultats dans une seule requête? La requête ci-dessus retournera seulement 1 ligne, peu importe ce que les LIMIT est fixé à. Si vous ajoutez GROUP BY, il va retourner tous les résultats, mais la COUNT sera inexacte
- Le résultat du comptage est utile de calculer combien de pages seront nécessaires pour voir le nombre total de lignes.
- count(*) a été une erreur, évidemment... j'ai mis à jour la requête pour inclure x,y,z colonnes.
- Je suis en retard pour la question et la réponse. Cela vous donnera un "bouton suivant", mais comment savez-vous si il va y avoir un page 9, à moins que vous interroger en premier? Si vous êtes à la recherche d'un côté et à l'arrière, j'aime cette approche!
- En gros une très bonne idée, cependant, si vous voulez que vos pages vous indique le nombre de pages que vous avez de l'exemple Page: 3 / 12 alors ce ne sera pas vous aider ici. Aussi l'affichage de la dernière page, le bouton n'est pas possible de cette façon.
- C'est l'une des approches recommandées par Percona: percona.com/blog/2008/09/24/...
InformationsquelleAutor Derrick
24

Une autre approche pour éviter la double interrogation est de récupérer toutes les lignes de la page actuelle à l'aide d'une LIMITE de la clause d'abord, puis seulement faire un second compte(*) de la requête si le nombre maximal de lignes ont été récupérés.

Dans de nombreuses applications, le résultat le plus probable sera que tous les résultats sur une seule page, et d'avoir à faire la pagination est l'exception plutôt que la norme. Dans ces cas, la première requête ne va pas récupérer le nombre maximum de résultats.

Par exemple, les réponses sur stackoverflow question rarement répandre sur une deuxième page. Commentaires sur la réponse rarement déborder la limite de 5 ou si nécessaire de leur montrer toutes.

Donc, dans ces applications, vous pouvez tout simplement faire une requête avec une LIMITE tout d'abord, et puis, tant que cette limite n'est pas atteinte, vous savez exactement combien de lignes il y a, sans le besoin de faire un second compte(*) une requête qui devrait couvrir la majorité des situations.
- Excellent point!
- J'ai eu la même approche, cependant, a découvert une faille avec elle aujourd'hui. La dernière page de résultats ne sera alors la pagination des données. c'est à dire, disons que chaque page doit avoir 25 résultats, la dernière page ne sera probablement pas avoir beaucoup d', disons qu'il a 7... cela signifie que le count(*) ne sera jamais exécuté, et donc pas de pagination sera affiché à l'utilisateur.
- Non - si vous disons, 200 résultats, vous interrogez les 25 et vous obtenez seulement 7 de retour, qui vous indique que le nombre total de résultats est de 207 et vous n'avez donc pas besoin de faire une autre requête avec la fonction COUNT(*) parce que vous savez déjà ce qu'il va dire. Vous avez toutes les informations dont vous avez besoin pour afficher la pagination. Si vous rencontrez un problème avec la pagination ne pas montrer à l'utilisateur, alors vous avez un bug quelque part d'autre.
InformationsquelleAutor thomasrutter
14

Dans la plupart des situations, il est beaucoup plus rapide et consomme moins de ressources pour le faire dans les deux requêtes distinctes que de le faire en une seule, même si cela semble contre-intuitif.

Si vous utilisez SQL_CALC_FOUND_ROWS, puis pour les grandes tables, il rend votre requête beaucoup plus lent, beaucoup plus lent même que l'exécution de deux requêtes, la première avec un COUNT(*) et la deuxième avec une LIMITE. La raison pour cela est que SQL_CALC_FOUND_ROWS provoque la clause LIMIT être appliquée après de l'extraction de l'lignes au lieu de l'avant, de sorte qu'il récupère l'ensemble de la ligne de tous les résultats possibles avant d'appliquer les limites. Cela ne peut pas être satisfait par un indice parce qu'il va chercher les données.

Si vous prenez les deux requêtes approche, seul le premier de l'extraction de COUNT(*) et pas vraiment de l'extraction et de données réelles, ce qui peut être satisfait beaucoup plus vite, car il est généralement possible d'utiliser des indices et n'ont pas à aller chercher la ligne réelle des données pour chaque ligne qu'il regarde. Ensuite, la deuxième requête suffit de regarder le premier $offset+$limite de lignes et de revenir ensuite.

Ce post à partir de la base de la performance blog explique cette plus loin:

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Pour plus d'informations sur l'optimisation de la pagination, vérifiez ce post et ce post.

InformationsquelleAutor thomasrutter
2
```
query = SELECT col, col2, (SELECT COUNT(*) FROM `table`) AS total FROM `table` WHERE `some_condition` LIMIT 0, 10
```
- Cette requête retourne le nombre total d'enregistrement dans la table; pas le nombre d'enregistrements qui correspondent à l'état.
- Le nombre total d'enregistrements est ce qui est nécessaire pour la pagination (@Laurent).
- Oh, eh bien, il suffit d'ajouter le where clause à l'intérieur de la requête et vous obtenez le droit "total", avec les résultats paginées (page est sélectionnée avec le limit clause
InformationsquelleAutor Cris McLaughlin
2

Ma réponse peut être en retard, mais vous pouvez sauter la deuxième requête (à la limite) et juste de filtrer l'info par le biais de votre retour en fin de script. En PHP par exemple, vous pourriez faire quelque chose comme:
```
if($queryResult > 0) {
   $counter = 0;
   foreach($queryResult AS $result) {
       if($counter >= $startAt AND $counter < $numOfRows) {
            //do what you want here
       }
   $counter++;
   }
}
```
Mais bien sûr, lorsque vous avez des milliers de dossiers à examiner, il devient inefficace très rapide. Pré-calculée à compter peut-être une bonne idée de regarder dans.

Voici une bonne lecture sur le sujet:
http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf
- merci pour le partage de ce lien!
- Oui, ce lien est exactement a de précieuses informations.
- Le lien est mort, je suppose que c'est le bon: percona.com/files/presentations/ppc2009/.... Ne pas modifier, car vous ne savez pas si il est.
InformationsquelleAutor Kama
0

Vous pouvez réutiliser la plupart de la requête dans une sous-requête et de définir un identifiant. Par exemple, un film de requête qui trouve des films contenant la lettre 's' de la commande par le moteur d'exécution devrait ressembler à ceci sur mon site.
```
SELECT Movie.*, (
    SELECT Count(1) FROM Movie
        INNER JOIN MovieGenre 
        ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
    WHERE Title LIKE '%s%'
) AS Count FROM Movie 
    INNER JOIN MovieGenre 
    ON MovieGenre.MovieId = Movie.Id AND MovieGenre.GenreId = 11
WHERE Title LIKE '%s%' LIMIT 8;
```
Ne remarque que je ne suis pas un expert base de données, et j'espère que quelqu'un sera en mesure d'optimiser un peu mieux. Comme il se exécute directement à partir de la ligne de commande SQL interface ils ont tous deux se ~0.02 secondes sur mon ordinateur portable.

InformationsquelleAutor Philip Rollins
-13
```
SELECT * 
FROM table 
WHERE some_condition 
ORDER BY RAND()
LIMIT 0, 10
```
- Cela ne veut pas répondre à la question, et une commande par rand est une très mauvaise idée.
InformationsquelleAutor John

Vous devez vous connecter pour publier un commentaire.