Pagination MySQL sans double interrogation?

Je me demandais s’il y avait un moyen d’obtenir le nombre de résultats d’une requête MySQL, tout en limitant les résultats.

La façon dont la pagination fonctionne (si je comprends bien), d’abord je fais quelque chose comme

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

Après avoir reçu le num_rows (query), j’ai le nombre de résultats. Mais pour limiter réellement mes résultats, je dois faire une seconde requête comme:

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

Ma question: existe-t-il de toute façon à la fois l’extraction du nombre total de résultats obtenus et la limitation des résultats renvoyés dans une seule requête? Ou tout moyen plus efficace de le faire. Merci!

Non, c’est le nombre d’applications qui veulent paginer. C’est fiable et à toute épreuve, même si cela rend la requête deux fois. Mais vous pouvez mettre le compte en mémoire quelques secondes et cela vous aidera beaucoup.

L’autre méthode consiste à utiliser la clause SQL_CALC_FOUND_ROWS , puis à appeler SELECT FOUND_ROWS() . Mis à part le fait que vous devez mettre l’appel FOUND_ROWS() après, il y a un problème avec ceci: il y a un bogue dans MySQL qui affecte les requêtes ORDER BY ce qui le rend beaucoup plus lent sur les grandes tables que l’approche naïve de deux requêtes .

Je ne fais presque jamais deux requêtes.

Il vous suffit de retourner une ligne de plus que nécessaire, d’afficher seulement 10 sur la page et, s’il ya plus que ce qui est affiché, d’afficher 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 être renvoyée dans l’ordre le plus pertinent en premier. Les chances sont, la plupart des gens ne vont pas se soucier d’aller à la page 236 sur 412.

Lorsque vous effectuez une recherche sur Google et que vos résultats ne figurent pas sur la première page, vous accédez probablement à la page deux, pas à neuf.

Une autre approche pour éviter la double requête consiste à récupérer toutes les lignes de la page en cours en utilisant d’abord une clause LIMIT, puis à ne faire qu’une deuxième requête COUNT (*) si le nombre maximal de lignes a été récupéré.

Dans de nombreuses applications, le résultat le plus probable sera que tous les résultats tiennent sur une seule page et que la pagination est l’exception plutôt que la norme. Dans ces cas, la première requête ne récupérera pas le nombre maximal de résultats.

Par exemple, les réponses à une question de stackoverflow sont rarement diffusées sur une deuxième page. Les commentaires sur une réponse dépassent rarement la limite de 5 ou plus pour les montrer tous.

Donc, dans ces applications, vous pouvez simplement faire une requête avec un LIMIT en premier, et tant que cette limite n’est pas atteinte, vous savez exactement combien de lignes il y a sans avoir à faire une seconde requête COUNT (*). couvrir la majorité des situations.

Dans la plupart des cas, il est beaucoup plus rapide et moins gourmand en ressources de le faire en 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, alors, pour les tables volumineuses, votre requête sera beaucoup plus lente, beaucoup plus lente que l’exécution de deux requêtes, la première avec un COUNT (*) et la seconde avec une LIMIT. La raison en est que SQL_CALC_FOUND_ROWS entraîne l’application de la clause LIMIT après l’ extraction des lignes au lieu de la précédente. Elle récupère donc l’intégralité de la ligne pour tous les résultats possibles avant d’appliquer les limites. Cela ne peut pas être satisfait par un index car il récupère réellement les données.

Si vous prenez l’approche des deux requêtes, la première ne récupère que COUNT (*) et ne récupère pas réellement les données, elle peut être satisfaite beaucoup plus rapidement, car elle peut généralement utiliser des index et ne pas chercher les données de ligne réelles pour chaque ligne qu’il regarde. Ensuite, la seconde requête doit uniquement examiner les premières lignes $ offset + $ limit, puis revenir.

Cet article du blog de performance MySQL explique ceci:

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, consultez ce post et ce post .

 query = SELECT col, col2, (SELECT COUNT(*) FROM `table`) AS total FROM `table` WHERE `some_condition` LIMIT 0, 10 

Ma réponse est peut-être tardive, mais vous pouvez ignorer la deuxième requête (avec la limite) et filtrer les informations via votre script back-end. 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 d'enregistrements à prendre en compte, cela devient très vite inefficace. Compte pré-calculé peut-être une bonne idée à examiner.

Voici une bonne lecture sur le sujet: http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf

Vous pouvez réutiliser la majeure partie de la requête dans une sous-requête et la définir sur un identifiant. Par exemple, une requête de film qui trouve des films contenant le classement de la lettre par runtime ressemblerait à 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; 

Notez que je ne suis pas un expert en bases de données et j’espère que quelqu’un sera en mesure de l’optimiser un peu mieux. Comme il s’exécute directement depuis l’interface de ligne de commande SQL, ils prennent tous les deux environ 0,02 seconde sur mon ordinateur portable.

 SELECT * FROM table WHERE some_condition ORDER BY RAND() LIMIT 0, 10