Utilisation de LAST_INSERT_ID()

La fonction MySQL LAST_INSERT_ID() permet de récupérer la valeur du dernier incrément généré lors de l'insertion de données dans une table contenant une colonne auto_increment.
Il peut être utile de récupérer cette valeur pour la réinjecter dans une seconde table. J'ai par exemple sur mon serveur les 2 tables suivantes :

mysql [localhost] {msandbox} (test) > select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | c |
| 2 | c2 |
| 3 | c3 |
+----+------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > select * from t2;
+------+------------+
| id | dt |
+------+------------+
| 2 | 2008-12-09 |
+------+------------+
1 row in set (0.00 sec)

Ayant eu l'occasion de vérifier le code de certains développeurs j'ai repéré du code utilisant cette fonction mais faisant appel à la table en question sans pour autant que cela soit nécessaire pour le résultat. Je me suis alors posé la question suivante : Y a t il un impact à faire appel à la table sans que cela soit nécessaire ?
Si on regarde les 2 plans d'exécution cela donne :

mysql [localhost] {msandbox} (test) > explain select last_insert_id();
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select last_insert_id() from t2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

L'appel à la table semble avoir besoin d'accéder à une ligne. Cela semble louche...

Vous l'aurez compris il faut bien sûr bencher pour savoir s'il y a une grosse différence. Pour cela MySQL met à disposition la fonction BENCHMARK() qui n'est pas très connue :

mysql [localhost] {msandbox} (test) > insert into t1(name) values('c4');
Query OK, 1 row affected (0.01 sec)

mysql [localhost] {msandbox} (test) > SELECT BENCHMARK(1000000,(SELECT last_insert_id() from t2));
+------------------------------------------------------+
| BENCHMARK(1000000,(SELECT last_insert_id() from t2)) |
+------------------------------------------------------+
| 0 |
+------------------------------------------------------+
1 row in set (5.09 sec)

mysql [localhost] {msandbox} (test) > SELECT BENCHMARK(1000000,(SELECT last_insert_id()));
+----------------------------------------------+
| BENCHMARK(1000000,(SELECT last_insert_id())) |
+----------------------------------------------+
| 0 |
+----------------------------------------------+
1 row in set (0.03 sec)

Voilà donc notre réponse. Accéder à la table, chose inutile pour notre résultat, prend plus de temps.

Calcul de la médiane en SQL

Depuis Oracle 10g il est possible d'effectuer le calcul de la médiane très facilement en utilisant la nouvelle fonction MEDIAN disponible.
Je suppose que vous disposez d'une table data constituée de 2 colonnes semaine et duree. Pour calculer la médiane sous Oracle vous n'avez plus qu'à utiliser la requête suivante :

SELECT semaine,MEDIAN(duree) FROM data GROUP BY semaine;

Pour MySQL c'est beaucoup plus compliqué puisqu'on ne dispose pas de cette magnifique fonction. J'ai donc fait quelques recherches et je suis tombé sur les travaux de frédéric Brouard. J'ai pu trouver la procédure adéquate basée sur la solution de Chris Date.

En supposant que vous disposez toujours de la table data qui chez moi utilise le moteur CSV, la méthode est la suivante :

CREATE TABLE d AS SELECT * FROM data;
ALTER TABLE d ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY FIRST, ADD INDEX (semaine,duree) ;

SELECT semaine,round(AVG(duree),2) AS MEDIANE
FROM (
   SELECT semaine,MIN(duree) AS duree
   -- valeurs au dessus
   FROM
   (
       SELECT ST1.semaine,ST1.id, ST1.duree
       FROM    d AS ST1
       INNER JOIN d AS ST2
       ON ST1.duree <= ST2.duree AND ST1.semaine=ST2.semaine
       GROUP BY ST1.id, ST1.duree
       HAVING COUNT(*) <= (SELECT CEILING(COUNT(*) / 2.0)
       FROM d d2 where d2.semaine=ST1.semaine)
   ) SUR
   GROUP BY semaine
   UNION
   SELECT semaine,MAX(duree) AS duree
   -- valeurs en dessous
   FROM
   (
       SELECT ST1.semaine, ST1.duree
       FROM    d AS ST1
       INNER JOIN d AS ST2
       ON ST1.duree >= ST2.duree AND ST1.semaine=ST2.semaine
       GROUP BY ST1.id, ST1.duree
       HAVING COUNT(*) <= (SELECT CEILING(COUNT(*) / 2.0)
       FROM d d2 where d2.semaine=ST1.semaine)
   ) SOU
   GROUP BY semaine
   ) T
GROUP BY semaine;

DROP TABLE d;

Sous Oracle, la requête est très rapide et il n'est pas nécessaire de créer un index particulier. Par contre sous MySQL, si vous vous amusez à supprimer l'index vous le ressentirez très vite si vous disposez de beaucoup de données. Dans mon cas, ma table contient 4874 lignes. Sans l'index, la requête dure près de 5 minutes contre moins de 5 secondes une fois indexée.

Bienvenue à MySQL 5.1 GA

Si vous surfez régulièrement sur la blogosphère MySQL, vous avez pu voir que la release MySQL 5.1 est enfin sortie. Elle était prévue pour le 6 décembre mais finalement elle est sortie 10 jours plus tôt. Ainsi, la version 5.1.30  est la première GA (Generally Available) release. Remercions aussi l'équipe Debian MySQL Maintainers qui a déjà sorti le paquet debian pour l'architecture amd64 ce qui démontre une certaine réactivité.

Cependant, il ne faut pas perdre à l'esprit que la version 5.1.31 commence déjà à montrer son nez pour corriger certains bugs.

Enfin pour un petit récapitulatif des nouvelles fonctionnalités de cette version n'hésitez pas à lire l'article de Jay Pipes sur le sujet "MySQL 5.1 Article Recap" ou a lire la section "What's New in MySQL 5.1" de la documentation MySQL.

Blogged with the Flock Browser

Utilisation du moteur Blackhole de MySQL

Le moteur Blackhole, comme son nom peut le sous-entendre, ne stocke aucune information. Tous les ordres DML sont effectués mais les données ne sont pas enregistrées.

Voici quelques cas où il devient intéressant d'utiliser ce moteur :
* Estimer la taille de vos binlogs générer en simulant un nombre hypothétique de requêtes par secondes sur votre serveur avec vos tables utilisant le moteur Blackhole
* Utiliser une table Blackhole pour générer des ordres DML sur d'autres tables sans avoir besoin de stocker d'information
* Peut jouer le rôle de distributeur de binary logs pour soulager la charger i/o d'un maître

Nous allons étudier la deuxième proposition qui consiste à utiliser une sorte de table de routage pour nos ordres DML.

Création d'une table Blackhole :

CREATE TABLE `mes_logs` (
`program` enum('MySQL','Apache','Autre',) DEFAULT NULL,
`msg` varchar(1000) DEFAULT NULL
) ENGINE=BLACKHOLE;

Création d'un trigger :

DELIMITER ;;
CREATE TRIGGER `redirect_logs` BEFORE INSERT ON `mes_logs` FOR EACH ROW BEGIN

IF ucase(NEW.program)='MYSQL' THEN
INSERT INTO mysql_logs VALUES(NEW.msg);

ELSEIF ucase(NEW.program)='APACHE' THEN
INSERT INTO apache_logs VALUES(NEW.msg);

ELSE
INSERT INTO autre_logs VALUES(NEW.msg);

END IF;
END */;;
DELIMITER ;

A partir de là, toute insertion dans la table mes_logs déclenchera le trigger redirect_logs qui mettra à jour la table correspondant au type de log tandis que les données ne seront pas stockées dans la table mes_logs. Ceci permet d'effectuer le routage des données de logs au niveau MySQL et non applicatif, dans le but par exemple d'archiver les tables, de les partitionner (et de dépasser la limite de 1024 sous partitions pour une table), de les vider sans influer sur les autres types de logs et tout en étant transparent pour les utilisateurs. Il sera aussi possible de modifier le trigger pour modifier ce routage.

Blogged with the Flock Browser

L'importance des headers de Mysqldump

Ne vous est il jamais arrivé d'effectuer un dump MySQL et de n'en récupérer seulement qu'une partie pour la réinjecter sur un autre serveur ?

Eh bien, si c'est le cas, prenez soin de toujours récupérer les entêtes du dump. A titre d'exemple, depuis la version 5.0.15 MySQL s'assure d'affecter la time zone UTC à la connection de mysqldump.

Il le fait en rajoutant dans l'entête du dump :

SET TIME_ZONE='+00:00'

Si vous retirez cette entête et que vous injectez des dates sur un autre serveur qui a par exemple la même timezone (UTC +1) et bien vous obtiendrez 1 heure de décalage dans les données de type date/datetime injectées sur votre nouveau serveur.

A mûrement réfléchir...