Accès mysql et recherche paginée

[zedark]

Bonjour !

J'ai mis en place sur mon site (php6+mysql) une recherche simple par nom. La requête est un bête SELECT sur 2 champs, et les résultats sont paginés.

Pour la pagination j'ai besoin du nombre total de résultats retournés par la recherche, d'où dans le code 2 requêtes successives, via PDO :

• Un SELECT COUNT(*) pour récupérer le total ;
  
• un SELECT * LIMIT 0,30 pour récupérer les résultats à afficher sur la première page.
  

Puis, à force de lire partout que pour rendre son code plus véloce il faut limiter au maximum le nombre d'appels à la BDD, j'ai essayé une solution à une seule requête :

• Un SELECT * pour tout récupérer ;
  
• je stocke dans un tableau, qui me donne le nombre d'enregistrements retournés ;
  
• puis un array_splice() sur le tableau pour ne garder que les 30 premières lignes à afficher.
  

Puis je me suis demandé : au fait c'est laquelle la meilleure solution ?

Donc j'ai testé avec microtime() :

• Pour une centaine d'enregistrements à retourner, pas de différence significative de vitesse d'exécution ;
  
• Par contre avec l'ensemble de la table concernée (15 000 lignes), la méthode à 2 requêtes est 75 fois plus rapide !
  

Voyez-vous d'autres critères que la vitesse d'exécution pour orienter mon choix ?

Merci,

[Kioob]

Bonjour,

la deuxième méthode n'a à mon sens aucun intérêt : tu "télécharges" toute la base de données, la stocke en mémoire (oui oui...), pour n'en utilise qu'une infime partie... C'est un peu comme photocopier tout un bouquin juste pour en lire le sommaire quoi.

Quand à l'optimisation, il n'y a rien de "générique" pour ce genre de choses, c'est du cas par cas. Ormis peut être de virer ce vilain "SELECT *" et de se limiter aux seuls champs nécessaires à ta page.

PS : en passant, je ne connais pas ta procédure de mesure, mais microtime() en elle même est une procédure assez lente, qui peut fausser les mesures.

[zedark]

Bonjour et merci pour la réponse rapide

la deuxième méthode n'a à mon sens aucun intérêt : tu "télécharges" toute la base de données, la stocke en mémoire (oui oui...), pour n'en utilise qu'une infime partie...

Oui, le résultat de mon petit test ne me laisse d'ailleurs aucun doute. J'avais juste l'idée que pour un nombre restreint d'enregistrements retournés (et j'estime que c'est le cas pour ma recherche par nom), le fait d'avoir une seule requête pouvait accélérer un peu le code. Je me trompais.

Quand à l'optimisation, il n'y a rien de "générique" pour ce genre de choses, c'est du cas par cas. Ormis peut être de virer ce vilain "SELECT *" et de se limiter aux seuls champs nécessaires à ta page.

Désolé j'ai oublié de préciser que ce ne sont pas les requêtes utilisées, j'ai mis ça pour ne pas alourdir mon post. Il s'agit en fait d'un :

SELECT a.card_name_en AS name_en,[...] b.edition_code
FROM card a JOIN card_edition b ON a.card_id = b.card_id
WHERE (card_name_fr LIKE :cardname OR card_name_en LIKE :cardname)
GROUP BY a.card_id ORDER BY card_name_en;

PS : en passant, je ne connais pas ta procédure de mesure, mais microtime() en elle même est une procédure assez lente, qui peut fausser les mesures.

Comme c'est la première fois que je joue à tester la rapidité d'exécution d'un bout de code, j'ai appliqué l'exemple de la page microtime() du manuel php :

$time_start = microtime(true);
for ($a=0; $a<100; $a++) {
 ...mon code à tester...
}
$time_end = microtime(true);
$time = $time_end - $time_start;
echo $time;

S'il y a d'autres moyens pour tester plus fiables (sans être trop compliqués à mettre en uvre), je suis preneur, car je compte renouveler l'expérience pour améliorer petit à petit mon vilain code d'amateur

[Kioob]

Pour ce qui est du benchmark, il y a divers points qui entre en jeu :

1) il faut s'assurer que le nombre de traitement dans la boucle soit suffisamment élevé pour que le cout des autres éléments (tels que microtime()) soit négligeable. Ainsi je choisi toujours mon nombre d'itération de manière à ce que la boucle dure au moins une seconde.

2) MySQL a un excellent cache, qui selon le contexte fausse les résultats d'un benchmark. Il peut être intéressant de re-tester les requêtes en question en ajoutant SQL_NO_CACHE juste après le mot clé SELECT.

Maintenant pour ce qui est du SQL en lui même, un GROUP BY un OR et deux LIKE, c'est clair que ce n'est pas forcément l'idéal, mais tu n'as peut être pas le choix ?

[cyberlaura]

Bonjour,

j'ai lu un jour, sur ce forum même, qu'il existait une méthode en Mysql pour récupérer le nombre d'enregistrements tout en utilisant le limit. Je ne me souviens plus de l'ordre en question mais peut-être quelqu'un s'en souvient-il ?

Je viens de retrouver ( enfin, merci Google ) : il s'agit de SQL_CALC_FOUND_ROWS et FOUND_ROWS()

Modifié 6 Avril 2009 par cyberlaura

[Kioob]

captain_torche a justement fait un post à ce sujet : http://www.webmaster-hub.com/index.php?showtopic=43498

[captain_torche]

Tu parles sans doute de mon article : "Gestion d'un système de pagination"

Il me semble toutefois que Kioob avait émis des réserves quant à la technique que j'avais utilisée.

[Edit] Woops, grillé ! (Et deux fois, en plus)

[cyberlaura]

Voilà, c'était exactement cet article. Merci Kioob ( et captain_torche ).

Ce qui me tue, c'est que ça date d'octobre, alors qu'il me semblait que ça n'avait pas plus de 2 - 3 mois...

Modifié 6 Avril 2009 par cyberlaura

[zedark]

Merci Kioob pour l'info sur le cache mysql, je vais refaire les tests.

Pour ce qui est de la complexité de la clause WHERE, je ne vois pas vraiment comment faire plus simple, et ça va être bien pire par la suite avec la mise en place d'une recherche multi-critères sur la même jointure. Par contre j'utilise si souvent cette jointure dans mes requêtes que je vais essayer de dénormaliser les deux tables pour voir ce que ça donne (une a 10 000 lignes, l'autre 15 000).

J'avais lu le post de captain_torche, mais j'ai écarté la solution avec SQL_CALC_FOUND_ROWS pour une raison dont je ne suis pas très fier : en local je n'ai aucun souci, mais chez l'hébergeur je n'arrive pas à réutiliser un objet PDO, la connection se referme systématiquement après chaque récupération de données. Et j'ai contourné le problème sans chercher la solution pour l'instant, mea culpa

Mais promis ce soir je m'y met.

Merci pour vos réponses,