{Résolu} [MySQL] Pagination sans LIMIT - Question d'optimisation

Plop !
Je travaille sur un livre d’or pour un site en Flash et je bloque sur un truc tout bête (enfin j’espère…).
On va dire que j’ai une table livre_or avec trois champs (id_message clé primaire autoincrémentée, auteur et message).

Comment remplacer le LIMIT dans cette requête ?

SQL
[color=blue;font-weight:bold]SELECT[/color] auteur, message FROM livre_or ORDER BY id_message DESC LIMIT $debut, $nb_messages;

$debut et $nb_messages étant des variables fixées par PHP ?

J’ai peut-être une solution mais je sais pas si c’est vraiment mieux.
Vu que mon champ id est une clé primaire, y aua un index dessus. Peut-être récupérer d’abord l’id du dernier champ à afficher et rajouter une clause BETWEEN après me fera gagner du temps ?

En fait, ça ferait deux requêtes :

SQL
[color=blue;font-weight:bold]SELECT[/color] id_message FROM livre_or ORDER BY id_message DESC LIMIT $debut, $nb_messages;

puis je récupère l’id le moins élevé (d’ailleurs je sais pas trop comment, vu que si je peux pas faire de MIN() je crois) et…

SQL
[color=blue;font-weight:bold]SELECT[/color] auteur, message FROM livre_or WHERE id_message BETWEEN $debut AND $id_moins_eleve;

PS : j’ai simplifié, y aura plus de trois champs, par exemple je stockerai un timestamp et l’ip de l’auteur.

Pourquoi il te gène le LIMIT, il sert à ça non ?

C’est beaucoup beaucoup plus lent qu’une requête équivalente avec un BETWEEN…

Peut-être, sauf qu’en utilisant LIMIT, tu ne lances qu’une requête, alors qu’avec BETWEEN, tu en exécutes 2…

Alors je ne veux pas être méchant, mais à un moment donné, faut arrêter de chercher à gagner des pouillèmes de millisecondes sur l’exécution d’un code pour en perdre une centaine de l’aute coté :sarcastic:

Oui mais deux requêtes, c’est pas forcément plus lent, surtout si l’une ne touche qu’à l’index… en fait je cherche la solution générale, regarde les benchs de vtuning sur le topic concernant son système de forums, la différence est assez élevée sur un nombre de posts importants (ça peut se compter en secondes !).

Edit : le lien vers les benchs http://www.clubic.com/forum/page-2-t418626-s25.html

La requête avec BETWEEN doit être en effet plus performant, puisque ça utilise pleinement l’index.

Mais la première, pour chercher les identifiants, j’ai des doutes…

Donc le LIMIT serait plus rapide ?
J’ai trouvé ça.

Non. Un limit c’est juste effectuer la requête sans LIMIT, et ignorer tous les résultats en dessous de l’offset de départ.

BETWEEN, c’est aller dans le B-Tree (je suppose), prendre les index adéquats : si la structure est bien faite, tu peux avoir sous le noeud “idmax” tous les id inférieurs à idmax.

En soit, c’est plus rapide que LIMIT car tu utilise les INDEX.

Sauf que là, tu fais une seconde requête qui si j’ai bien compris utilise LIMIT, pour faire la même chose que celle du post initial, mais juste avec id_message : ça n’a rien de rapide pour moi.

Ca, ça peut l’être :

SELECT MAX(id_message) FROM table;

Car ça utilise l’index :slight_smile:

Je parlais du seul LIMIT par rapport à mes deux requêtes. Je vois pas trop comment je pourrais remplacer ma première requête ? Je peux pas me baser sur le dernier, puisqu’il peut y avoir des trous…
Du coup si t’as une autre solution, je suis preneur ! :slight_smile:

[STRIKE]Je trouve qu’il est quand même vachement mal expliqué le site de Mysql… Hum et une selection en utilisant la clé primaire du genre :

SELECT * FROM toto WHERE id_message>$limit_inf AND id_message<$limit_sup

C’est plus lent :confused: ?[/STRIKE]

Chui trop con ça revient au BETWEEN -_- !

Alors puisque je n’ai pas du tout le temps, voici rapidemen tun c/c du système que j’utilise pour mon forum (utilisant le BETWEEN).

A toi d’en faire ce que tu en veux :slight_smile:

$messages_par_page = 50;

$sql_count_posts = mysql_fetch_assoc(mysql_unbuffered_query("SELECT last_pidintopic FROM forum_topics WHERE tid='".$_GET['topic']."'")); // Ce champs contient le nombre de posts
$nombre_pages  = ceil($nombre_de_messages / $messages_par_page);
$i = 1;
while ($i <= $nombre_pages-1)
{
	$i++;
}

if(isset($_GET['page']) AND is_numeric($_GET['page']) AND $_GET['page']<=$nombre_pages)
{
	$np = $_GET['page'];
}

else
{
	$np = 1;
}

$id_premier_message = ($np - 1) * $messages_par_page;
[...]
$from = $messages_par_page*$np-$messages_par_page;
$to = $from + $messages_par_page;

$query = "  
SELECT bla,bla,bla,bla FROM xyzz WHERE	x BETWEEN '".$from."' AND '".$to."'  ORDER BY xxxyyyzzz";
mysql_query($query)

Voila simplifié rapidement

Je suis en retard mais je n’avais pas vu ce post avant !

Note: le gain par rapport à un LIMIT est indéniable :wink:

Déjà, merci pour la réponse. :slight_smile:
J’ai regardé ça avec attention.

J’ai juste supprimé ce qui était redondant pour plus de clarté dans le code.

Une question : comment tu fais si un enregistrement est supprimé ?
Tu remets à jour les id à chaque fois ? Parce que là, si les enregistrements d’id $from et les cinq qui suivent par exemple sont supprimés, tu vas te retrouver avec cinq messages de moins d’affichés… ou alors j’ai rien compris. :paf:

Oui, tu décrémentes toutes les ids supérieures à celle du message supprimé (attention, pas les id auto-incrémentées si auto-incrémentées il y a, celles propres à ta gestion de messages). C’est assez facile à faire et franchement, ce n’est pas vraiment difficile à supporter pour le serveur (surtout si tu supprimes dans les 1000 derniers messages). Et puis si on prend le ratio messages postés, vus/messages supprimés, l’avantage étant au premier, il est plus judicieux d’améliorer l’affichage général plutôt que le système de suppression…

Donc en gros oui en cas de suppression de message ça pourrait demander un peu de travail au serveur (quoique, ça se fait ressentir au bout de quelques milliers de messages, donc y’a de la marge pour un livre d’or…); mais sinon c’est beaucoup plus optimisé et légèr comme méthode que celle du vieux LIMIT tout bourrin qui doit traiter beaucoup d’informations inutiles avant d’atteindre celles désirées.

OK, je vais y réfléchir merci :slight_smile:

5 semaines plus tard, la solution :

SQL
[color=blue;font-weight:bold]SELECT[/color] auteur, message FROM livre_or ORDER BY id_message DESC LIMIT $debut, $nb_messages;

peut aussi s’écrire :

SQL
SET @nb_messages= 0;

[color=blue;font-weight:bold]SELECT[/color] auteur, message, @nb_messages := @nb_messages + 1
FROM livre_or WHERE id_message >= $debut AND @nb_messages < $nb_messages
ORDER BY id_message DESC;

:nico:

:ouch: je savais pas qu’on pouvait incrémenter en sql !

Sauf que dans l’absolu, il manque un DECLARE @nb_messages int;

http://dev.mysql.com/doc/refman/5.0/en/declare.html

(sinon ça ne marche pas dans MSSQL et d’autres je parie)

('fin pour ce que j’en dis, encore un cas d’optimisation fortement douteux).

premature optimisation is root of evil.

Fais gaffe quand-même, c’est pas du SQL standard au niveau syntaxique.
Mais les benchs le prouvent, c’est beaaaaaaaaaucoup plus rapide qu’un LIMIT en fin de table quand y a pleins d’enregistrements : ça te permet d’utiliser uniquement l’index.

C’est pas un vrai bloc, c’est de la bidouille MySQL.
C’est pas fait pour être utilisé à toutes les sauces, juste pour répondre à des cas d’utilisation précis, où les performances comptent davantage que la portabilité (un forum par exemple).

Edit :

J’ai regardé pour ça aussi, quand tu fais un SELECT sur un seul champ avec un index, MySQL parcourt le fichier d’index au lieu de parcourir la table. Ce fichier, plus petit, peut même déjà être en mémoire dans certains cas… du coup tu gagnes forcément du temps.

(enfin je dis ça à titre informatif, hein, pour faire profiter les intéressés de mes recherches patapay !)