Archives par mot-clé : PgSQL

Mysql et la recherche sur plusieurs champs : avec CONCAT ou CONCAT_WS

Publié dans SQL | 3 commentaires
27 Oct 2009

Après avoir parlé un peu de la recherche en MySQL via LIKE puis REGEXP, et avant d’aller plus loin, parlons un peu de la recherche dans plusieurs champs à la fois. Eh oui, c’est possible grâce à CONCAT, ou plutôt CONCAT_WS.
Prenons une table contenant les billets d’un blog, et donc possédant les champs id, titre et contenu. Une recherche classique cherchera un mot clef dans le titre et dans le contenu du billet. Pour cela, on pourrait utiliser OR, mais un CONCAT qui concatène (i.e. met l’un à la suite de l’autre) le contenu de titre et de contenu, c’est pas mal aussi ! Exemple à l’appui :

SELECT * FROM billet WHERE CONCAT(titre, contenu) LIKE "%mot%clef%";

On peut ainsi concaténer autant de champs que l’on souhaite. Un petit détails cependant, si l’un de ces champs est NULL, le résultat renvoyé est NULL. Ce qui implique qu’un article sans titre dont le contenu correspond au mot clef, ne sera pas récupérée. Même si l’exemple est débile, c’est un peu dommage. Pour éviter cela, il convient d’utiliser CONCAT_WS qui fonctionne de la même manière, sans se soucier des champs vides.
Le deuxième exemple ci-dessous est certes moins compréhensible, mais il illustre ce dont nous venons de parler, et explique bien l’intérêt d’utiliser CONCAT plutôt que OR dans certains cas.

SELECT * FROM billet WHERE CONCAT_WS(date, ' ', heure, 'h', minute) LIKE "10/11/2009 23h30";

Bien sûr, il est possible d’utiliser la commande REGEXP au lieu de LIKE ! Bref, vive la concaténation !

Puisque ses rétroliens ne semblent pas fonctionner, merci à CrazyCat de G33k-zone pour m’avoir fait découvrir cette commande SQL bien utile.

Optimiser ses requêtes MySQL

Publié dans SQL | 4 commentaires
10 Juil 2009

Je travaille actuellement sur une table MySQL qui va contenir près de 7 millions de tuples (avec une quinzaine de colonnes chacun bien sûr, ce ne serait pas drôle sinon !), il se trouve qu’elle en contient déjà près de 3 millions, et que je suis obligé de faire une vingtaine de requêtes différentes dans cette table pour chaque page et que, par conséquent, le site qui l’utilise plante à fond. La mémoire utilisable par MySQL est dépassée. Alors certes, on peut modifier la taille de la mémoire utilisable par MySQL (et il faudrait aussi sûrement modifier la durée maximum d’exécution d’un script PHP), mais le but c’est quand même de charger des pages en moins de 30 secondes. J’ai donc cherché des réponses sur le Web et auprès des développeurs de ma boîte. Voici donc quelques points que j’ai pu retenir.

Structure de la table

Il vaut mieux éviter, sauf si c’est nécessaire, l’utilisation de champ NULL par défaut. En effet, comme on peut le voir sur cet article d’Apprendre PHP NULL est une valeur spéciale qui nécessite un traitement spécial, MySQL ne peut pas le tester avec un égal et ne peut le comparer à la chaine vide ou à 0, il est obligé d’utiliser IS ou le symbole équivalent <=>. NULL par défaut est d’autant plus à bannir pour les index. Utiliser des colonnes avec le meilleur type et la meilleure taille possible. Un prénom n’a par exemple par besoin d’être en VARCHAR(255), un VARCHAR(20) suffit plus que largement. Une bonne technique pour optimiser ses tables est d’utiliser PROCEDURE ANALYSE(). On créé ses tables au feeling (je parle juste de la structure, vous ne couperez pas à la normalisation tout ça, héhé), on les remplie avec un contenu à peu près définitif est on utilise une requête du style :

SELECT id, titre, message FROM blog PROCEDURE ANALYSE()

MySQL va nous répondre un joli petit array avec la taille max et min de chaque colonne id, titre et message, quelques autres informations intéressantes et surtout une proposition de type(taille) optimisée à votre table. Encore une fois, c’est l’article d’Apprendre PHP qui en montre un exemple vraiment parlant.
Continuer la lecture →

Une seule et même fonction pour différents systèmes de gestion de base de données

Publié dans PHP objet | 3 commentaires
03 Juin 2009

Je suis depuis longtemps déjà un féru utilisateur de FluxBB (anciennement PunBB), un système de forum bien fichu, valide W3C, très léger et donc configurable à souhait. En ce moment ce projet open source bat un peu de l’aile, mais de nouvelles mises à jours sortent encore assez régulièrement et la nouvelle version (attendue depuis des années) semble de nouveau repartir sur de bonnes bases. Mais bref, je parle de cela car FluxBB gère les bases de données MySQL, PostgreSQL (ou PgSQL) et même SQLite je crois. Pour se faire, au lieu d’avoir un switch ou des if else à chaque requête à effectuer, ils ont créé une class qui regroupe toutes les fonctions utiles (connect, query, fetch_array, …) et dans le code ils y font appelle après avoir instanciée cette class : $db->query, $db->fetch_array. Reste à sélectionner le type de sa base de données dans la partie administration et hop, on change de SGBD sans rien changer au code ! Pratique ! Je n’ai pas encore fini d’apprendre le PHP objet, mais j’avais envi de pratiquer un peu, alors sans regarder ailleurs, j’ai commencé à coder. Et voici une première version. Ma classe oublie surement pas mal de fonctions, et je n’ai pas pu la tester partout, et puis il manque la gestion des erreurs, j’aurai pu ajouter un compteur de requêtes, mais c’est ma première véritable class, alors soyez indulgent ^^

<?php
class Sgbd {
	public $nom;
	public function __construct($nom) {
		$this->nom = $nom;
	}
	public function connect($hote, $port, $nomBdd, $utilisateur, $mdp) {
		switch ($this->nom) {
			case 'pg':
			$requete = 'host='.$hote.' port='.($port == NULL ? '5432' : $port).' dbname='.$nomBdd.' user='.$utilisateur.' password='.$mdp;
			pg_connect($requete);
			break;
			default:
			mysql_connect($hote.($port == NULL ? ':3307' : ':'.$port), $utilisateur, $mdp);
			mysql_select_db($nomBdd);
		}
	}
	public function close() {
		switch ($this->nom) {
			case 'pg':
			pg_close();
			break;
			default:
			mysql_close();
		}
	}
	public function query($requete) {
		switch ($this->nom) {
			case 'pg':
			$result = pg_query($requete);
			break;
			default:
			$result = mysql_query($requete);
		}
		return $result;
	}
	public function fetch_array($result) {
		switch ($this->nom) {
			case 'pg':
			$arr = pg_fetch_array($result);
			break;
			default:
			$arr = mysql_fetch_array($result, MYSQL_BOTH);
		}
		return $arr;
	}
	public function fetch_row($result) {
		switch ($this->nom) {
			case 'pg':
			$row = pg_fetch_row($result);
			break;
			default:
			$row = mysql_fetch_row($result);
		}
		return $row;
	}
	public function num_rows($result) {
		switch ($this->nom) {
			case 'pg':
			$num_rows = pg_num_rows($result);
			break;
			default:
			$num_rows = mysql_num_rows($result);
		}
		return $num_rows;
	}
}
$sgbd = new Sgbd('mysql');
?>