Voici un petit mix de choses à savoir sur MySQL qui peuvent être utiles.

En passant des types de champs aux moteurs de stockage d’une table.

J’espère qu’une chose ou deux pourront vous être utile dans ce medley.

Types de champs

• CHAR : Pour des enregistrements ayant tous la même longueur, préférer le CHAR au VARCHAR. La taille des colonnes n’étant pas variable, MySQL pourra accèder à l’élément plus rapidement.
• TINYINT : ne pas mettre systématiquement du INT pour un entier si sa valeur ne tend pas à augmenter énormément. Penser également à mettre l’attribut UNSIGNED lorsqu’on souhaite seulement des valeurs positives.
• TIMESTAMP : un champ de ce type est très utile en lui mettant en valeur par défaut CURRENT_TIMESTAMP, et l’attribut ON UPDATE CURRENT_TIMESTAMP. De cette façon, la date sera automatiquement mise à jour à la création de l’enregistrement, mais également à chaque update.

 

Moteurs de stockages

• MyISAM : moteur rapide en lecture mais non-transactionnelles. A utiliser sur des tables accessibles en lecture pour plus de performances.
• InnoDB : moteur transactionnelles, utilisé par défaut depuis MySQL 5.6. A utiliser dans la plupart des cas. Les tables qui disposent de ce moteur ne verront pas leur dernière date de modification mise à jour (UPDATE_TIME).
• MEMORY : à utiliser avec précaution ! MySQL charges toutes les tables en mémoire, l’accès aux données est donc très rapide, mais si le serveur plante ou redémarre les données seront perdues. La date de création d’une table de ce type (CREATE_TIME) n’est disponible qu’à partir de MySQL 5.1.6.

 

Variables MySQL

Par défaut MySQL est paramétré pour fonctionner sur une machine assez modeste.

Voici une liste de variables à modifiées en fonction de la capacité de votre serveur, pour optimiser les temps de réponses.
Cela permet de jouer principalement sur la taille du cache. Les variables diffèrent suivant le moteur de stockage utilisé.

innodb_buffer_pool_size
key_buffer_size
tmp_table_size
max_heap_table_size
sort_buffer_size
join_buffer_size
table_open_cache
query_cache_limit
query_cache_size
Sread_rnd_buffer_size

Direction la doc de MySQL pour plus de détails.

 

Requête utile

Voici une requête qui peut être utile, elle permet de récupérer le dernier élément d’une table jointe, sans passer par une sous-requête.
Dans cet exemple, on récupère le dernier commentaire de chaque utilisateur :

SELECT
Utilisateur.nom,
Utilisateur.prenom,
SUBSTRING_INDEX(GROUP_CONCAT(Commentaire.commentaire SEPARATOR "|"), "|", -1) as dernierCommentaire
FROM Utilisateur
LEFT JOIN Commentaire USING(idUtilisateur)
WHERE Commentaire.commentaire IS NOT NULL
GROUP BY Utilisateur.idUtilisateur
ORDER BY Utilisateur.nom, Utilisateur.prenom

Le nombre d’éléments ramené par la fonction GROUP_CONCAT est limité par la variable group_concat_max_len (par défaut 1024).

Il est possible de changer cette valeur comme ceci :

SET [SESSION | GLOBAL] group_concat_max_len = val;

Attention donc à ne pas utiliser cette requête sur de gros résultats.

 

phpMyAdmin

Il est possible de modifier la config de phpMyAdmin directement dans le fichier /etc/phpmyadmin/config.inc.php.
Voici un exemple de paramètres modifiables :

$cfg['SuhosinDisableWarning'] = true;
$cfg['PropertiesIconic'] = true;
$cfg['AjaxEnable'] = false;

 

En vrac

• Le mot clef IGNORE permet à MySQL d’ignorer d’éventuels erreurs et de continuer le traitement.
• Dans un LIKE, le _ remplace n’importe quel caractère.
• Option –i-am-a-dummy.
• Une requête utile pour effectuer un dump d’une base vers une autre (en ligne de commande) :
  mysqldump -h localhost -u root -p --opt --disable-keys MaBDD | mysql -h localhost -u root -p -C MaBDD_Backup
• La même chose mais depuis un fichier SQL :
  mysql -h localhost -u root -p -c MaBDD < /tmp/Mon-fichier-SQL.sql
• Après un gros lot d’INSERT/DELETE/UPDATE, la cardinalité des index d’une table peut être complètement faux. Pour y remédier, utiliser ces 2 commandes : OPTIMIZE TABLE Matable et ANALYZE TABLE Matable