[SQL] une seule requête au lieu de deux...

Marsh Posté le 24-06-2008 à 10:17:52

Bonjour,

j'ai actuellement deux tables.

Code :

mysql> desc log;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+-------------------+----------------+
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
| TIMESTAMP | timestamp | YES | | CURRENT_TIMESTAMP | |
| USED | int(11) | | | 0 | |
| MSG_ID | tinyint(3) unsigned | | | 0 | |
+-----------+---------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

Id n'a pas besoin de description il me semble. Timestamp non plus.
Used est un pourcentage qui peut donc varier entre 0 et 100 (oui, je sais, tinyint)
et Msq_id peut être considéré comme une clé étrangère de la table Msg.

Code :

mysql> desc msg;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| MSG_ID | int(11) | | PRI | 0 | |
| MSG | varchar(150) | | | | |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Bon bah là, c'est clair, pas besoin d'explications.

Le but est de récupérer le dernier MSG et le USED pour chacun des MSG de la table MSG.

Exemple:

Code :

mysql> select * from msg;
+--------+--------------------------+
| MSG_ID | MSG |
+--------+--------------------------+
| 1 | Database error |
| 2 | Invalid or bad arguments |
+--------+--------------------------+
2 rows in set (0.00 sec)
mysql> select * from log;
+----+---------------------+------+--------+
| ID | TIMESTAMP | USED | MSG_ID |
+----+---------------------+------+--------+
| 1 | 2008-06-24 10:08:03 | 19 | 1 |
| 2 | 2008-06-24 10:08:11 | 29 | 2 |
| 3 | 2008-06-24 10:08:18 | 87 | 2 |
| 4 | 2008-06-24 10:08:44 | 50 | 1 |
| 5 | 2008-06-24 10:08:35 | 90 | 2 |
| 6 | 2008-06-24 10:08:28 | 99 | 1 |
+----+---------------------+------+--------+
6 rows in set (0.00 sec)

Dans ce cas, il faut que je récupère un truc du genre

Code :

+--------------------------+------+
| MSG | USED |
+--------------------------+------+
| Database error | 50 |
| Invalid or bad arguments | 90 |
+--------------------------+------+

Le MSG et non le MSG_ID, ainsi que le dernier USED pour chacun des MSG.
Bien sûr, il faut que cette requête reste opérationnelle lors de l'ajout d'un MSG dans la table MSG (pas de "limit 2" )

Actuellement, je fais ça en deux requêtes, mais vu le contenu important des deux tables, chaque parcours est assez couteux,
donc j'aimerais le faire si possible en une seule requête.

Reply

Marsh Posté le 24-06-2008 à 10:17:52

Reply

Marsh Posté le 24-06-2008 à 10:40:21

Code :

SELECT MSG, USED, MAX(TIMESTAMP) FROM MSG INNER JOIN LOG ON MSG.ID = LOG.MSG_ID GROUP BY MSG, USED

Message édité par Harkonnen le 24-06-2008 à 10:40:45

---------------
J'ai un string dans l'array (Paris Hilton)

Reply

Marsh Posté le 24-06-2008 à 10:50:05

Code :

mysql> SELECT MSG, USED, MAX(TIMESTAMP) FROM MSG INNER JOIN LOG ON MSG.MSG_ID = LOG.MSG_ID GROUP BY MSG, USED;
+--------------------------+------+---------------------+
| MSG | USED | MAX(TIMESTAMP) |
+--------------------------+------+---------------------+
| Database error | 19 | 2008-06-24 10:08:03 |
| Database error | 50 | 2008-06-24 10:08:44 |
| Database error | 99 | 2008-06-24 10:08:28 |
| Invalid or bad arguments | 29 | 2008-06-24 10:08:11 |
| Invalid or bad arguments | 87 | 2008-06-24 10:08:18 |
| Invalid or bad arguments | 90 | 2008-06-24 10:08:35 |
+--------------------------+------+---------------------+
6 rows in set (0.00 sec)

C'est presque ça, sauf que je veux uniquement le dernier enregistrement pour chaque MSG.
Or, à cause du USED qui varie tout le temps, on ne peut pas utiliser le DISTINCT.

Reply

Marsh Posté le 24-06-2008 à 11:14:06

http://dev.mysql.com/doc/refman/5. [...] p-row.html
si on part du principe que ton dernier enregistrement c'est TIMESTAMP, alors la logique mysql te donnerait ca :

Code :

select
m.msg,
l.used,
l.timestamp
from
msg m,
log l
where
timestamp=
(
select max(timestamp)
from log l2
where l2.msg_id=m.msg_id
);

j'aime pas trop mais bon si c'est ce qu'ils préconisent...

Reply

Marsh Posté le 24-06-2008 à 11:27:22

Et bien merci, ça a l'air de fonctionner
Même si je suis pas fan de ce genre de requêtes, au moins ça marche.

Reply

Marsh Posté le 01-07-2008 à 18:59:45

bah même si t'es pas fan, c'est la seule solution standard qui existe

Reply

Marsh Posté le 02-07-2008 à 09:39:38

Je suis pas fan, car celà implique l'utilisation d'un subselect,
or je ne suis pas assuré que le "produit" soit installé systématiquement sur une version MySQL supérieure à la 4.1

Reply

Marsh Posté le 02-07-2008 à 09:51:45

y'a peut-être moyen de le faire en une fois avec le 'hack' du substring en mysql... on en parlait dans ce topic et c'est la solution que j'ai adopté finalement.

Message édité par avander le 02-07-2008 à 09:54:04

Reply

Marsh Posté le 02-07-2008 à 13:13:50

Effectivement, j'ai simulé 50000 enregistrements dans ma table principale,
et je kill le process mysql au bout de 2 min de dépit
lorsque la requete est faite avec une sous requete.

Alors qu'avec la méthode du substring, ça ne met que 2 sec

Reply

Marsh Posté le 02-07-2008 à 15:52:10

Premièrement, voici les deux tables de test : http://pastebin.com/f23086f34

Et voici le script pour remplir les deux tables : http://pastebin.com/f708b2df3

Les résultats (query_cache=0 sur MySQL 4.1.20) pour (seulement) 5000 lignes dans la table log :

Code :

SELECT `MSG`, `USED_1`, `USED_2`, `USED_3`
FROM `msg` INNER JOIN `log` as l USING (`MSG_ID`)
WHERE `TIME` = (SELECT MAX(l1.`TIME`) FROM `log` AS l1 WHERE l1.`MSG_ID` = l.`MSG_ID`)
ORDER BY LENGTH(`MSG`), `MSG`;

10 rows in set (1 min 31.95 sec)

Code :

SELECT `MSG`, `USED_1`, `USED_2`, `USED_3`
FROM `msg`, `log` as l
WHERE `msg`.`MSG_ID` = l.`MSG_ID` AND
ROW(l.`MSG_ID`, l.`TIME`) IN ( SELECT `MSG_ID`, MAX(`TIME`)
FROM `log`
GROUP BY `MSG_ID`)
ORDER BY LENGTH(`MSG`), `MSG`;

10 rows in set (1 min 49.53 sec)

Code :

SELECT `MSG`, `USED_1`, `USED_2`, `USED_3`
FROM `msg`, `log` as l
WHERE `msg`.`MSG_ID` = l.`MSG_ID` AND
`TIME` = ( SELECT l1.`TIME`
FROM `log` as l1
WHERE l1.`MSG_ID` = l.`MSG_ID`
ORDER BY l1.`TIME` DESC
LIMIT 1)
ORDER BY LENGTH(`MSG`), `MSG`;

10 rows in set (7.20 sec)

Code :

SELECT `MSG`, SUBSTR(MAX(CONCAT(`TIME`,CONCAT_WS('-', `USED_1`, `USED_2`, `USED_3`))), LENGTH(`TIME`)+1) AS USED
FROM `msg`, `log` as l
WHERE `msg`.`MSG_ID` = l.`MSG_ID`
GROUP BY `msg`.`MSG_ID`;

10 rows in set (0.19 sec)

La même sur 50000 lignes dans la table log :
10 rows in set (1.83 sec)

Mon choix est fait :-)

Reply

Marsh Posté le 02-07-2008 à 15:52:10

Reply

Marsh Posté le 02-07-2008 à 16:13:47

si c'estaussi lent avec la sous-requête, c'est qu'il y a un problème dans ta requête ou tes index. normalement le plan d'exécution ne tiens pas compte de la sous-requête
genre, y'a un index (msg_id, time) dans ta table ?

bon, ceci dit, c'est une 4.1.2... vu que ça venait d'être implémenté, ils n'avaient peut-être pas encore fait les optimisations. mysql faut l'utiliser à partir de la 5 quand on fait du SQL "standard". avant, effectivement sorti des bidouilles y'a pas trop moyen de faire des requêtes correctes

Message édité par MagicBuzz le 02-07-2008 à 16:16:10

Reply

Marsh Posté le 02-07-2008 à 16:29:45

Les tables sont crées telle que décrit dans le script, donc pas d'index.

Et les résultats sont les mêmes sur une MySQL 5.0.51b

-- Edit --

Code :

mysql> EXPLAIN SELECT `MSG`, `USED_1`, `USED_2`, `USED_3`
-> FROM `msg` INNER JOIN `log` as l USING (`MSG_ID`)
-> WHERE `TIME` = (SELECT MAX(l1.`TIME`) FROM `log` AS l1 WHERE l1.`MSG_ID` = l.`MSG_ID`)
-> ORDER BY LENGTH(`MSG`), `MSG`\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: l
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50000
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: msg
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: test.l.MSG_ID
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: l1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50000
Extra: Using where
3 rows in set (0.00 sec)

Code :

mysql> EXPLAIN SELECT `MSG`, SUBSTR(MAX(CONCAT(`TIME`,CONCAT_WS('-', `USED_1`, `USED_2`, `USED_3`))), LENGTH(`TIME`)+1) AS USED
-> FROM `msg` INNER JOIN `log` USING (`MSG_ID`)
-> GROUP BY `msg`.`MSG_ID`\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: log
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 50000
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: msg
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: test.log.MSG_ID
rows: 1
Extra: Using where
2 rows in set (0.00 sec)

C'est plutôt évident avec un EXPLAIN

Message édité par carborom le 02-07-2008 à 16:34:15

Reply

Marsh Posté le 02-07-2008 à 17:21:14

c'est surtout évident que MySQL c'est de la merde

Création du jeu de test :

Code :

CREATE TABLE msg
(
    id integer PRIMARY KEY NOT NULL,
    label varchar(50) NOT NULL
)
go
 
INSERT INTO msg (id, label) VALUES (1, 'Database Error');
INSERT INTO msg (id, label) VALUES (2, 'Invalid or bad arguments');
 
CREATE TABLE log
(
    id integer PRIMARY KEY NOT NULL,
    ts datetime NOT NULL,
    used tinyint NOT NULL,
    msg_id integer NOT NULL
)
go
ALTER TABLE log ADD constraint fk_msg FOREIGN KEY (msg_id) REFERENCES msg(id)
go
 
declare @i AS integer;
SET @i = 1;
while @i < 50000
begin
  INSERT INTO log (id, ts, used, msg_id) VALUES (@i, getdate(), rand() * 100, rand() * 2 + 1);
  SET @i = @i + 1;
end;

Test :

Code :

declare @d1 AS datetime;
SET @d1 = getdate();
 
SELECT
  m.label,
  l.used,
  l.ts
FROM
  msg m,
  log l
WHERE
  ts=
  (
    SELECT max(ts)
    FROM log l2
    WHERE l2.msg_id=m.id
  );
 
print datediff(ms, @d1, getdate());

Sortie :

(48 ligne(s) affectée(s))

(1 ligne(s) affectée(s))
76

Sans index sur mon champ ts.
Sâchant qu'en plus j'ai un grand nombrede doublons (d'où les 48 lignes retournées, ce qui est normalement impossible, mais là ça vient de la création des lignes qui est trop rapide -avoir deux enregistrement dans la base à la même milliseconde, c'est pas de chance -)

Donc 76 ms pour retrouver 48 lignes sans passer par un index parmi 50000, c'est pas mal... Le plan d'exécution (bien plus détaillé que celui de MySQL) n'indique pas de subquery mais que des jointures régulières

SQL Server 2005 Express sur mon portable tout pourri qui swap à mort comme un con.

Message édité par MagicBuzz le 02-07-2008 à 17:26:00

Reply

Marsh Posté le 02-07-2008 à 17:32:20

En modifiant la requête (je prends id et non ts pour ne plus avoir de doublons) et en créant un index :

Code :

CREATE UNIQUE INDEX ix_1 ON log (id, msg_id);
 
declare @d1 AS datetime;
SET @d1 = getdate(); 
SELECT  m.label,  l.used,  l.ts FROM  msg m,  log l WHERE  l.id=  (    SELECT max(id)    FROM log l2    WHERE l2.msg_id=m.id  ); 
print datediff(ms, @d1, getdate());

(2 ligne(s) affectée(s))

(1 ligne(s) affectée(s))
30

(dans ton cas, l'index c'est sur timestamp, msg_id qu'il faut le créer)

impossible sous sql server de tester ta bidouille sans la sous-requête, mais voici une autre méthode ne nécessitant pas de sous-requête :

Code :

declare @d1 AS datetime;
SET @d1 = getdate(); 
 
SELECT m.label, l1.id, max(l2.id), l1.used
FROM msg m
INNER JOIN log l1 ON l1.msg_id = m.id
INNER JOIN log l2 ON l2.msg_id = m.id
GROUP BY m.label, l1.used, l1.id
HAVING l1.id = max(l2.id)
 
 
print datediff(ms, @d1, getdate());

(2 ligne(s) affectée(s))

(1 ligne(s) affectée(s))
63

On voit qu'on rajoute une étape au MÊME plan d'exécution, du coup ça relenti tout

Bref : la méthode avec sous-requête est bien plus performante.

Message édité par MagicBuzz le 02-07-2008 à 17:41:34

Reply

Marsh Posté le 02-07-2008 à 17:36:17

Pourquoi détourner le problème vers SQL Server 2005 Express ?

On m'impose MySQL, on m'impose d'être rétro-compatible (minimum 4.0),
et je ne peux pas toucher à la structure de la table (la création d'index est exclue)
je n'ai ABSOLUMENT aucun intérêt à voir ce que donne la requête sur un autre gestionnaire de BDD ...

Ce que mon post précédent voulait dire c'est que pour certaines requêtes,
même si la solution "parait" évidente (l'utilisation de la sous-requête)
il vaut mieux tester dans un environnement "réel" ou s'y rapprochant le plus possible.

Message édité par carborom le 02-07-2008 à 17:37:43

Reply

Marsh Posté le 02-07-2008 à 17:44:39

c'est juste que moi je dis que je jette l'éponge quand il s'agit d'écrire une requête pour mysql.

sont pas foutus d'avoir un moteur de requête qui se comporte comme escompté... c'est limite pire qu'Oracle...

Reply

Marsh Posté le 02-07-2008 à 17:46:11

ps: et ne pas créer d'index sur ce genre de table, c'est du suicide... une table de log, ça peut rapidement contenir des millions d'enregistrement, mais leur ajout n'est pas suffisant pour pouvoir ralentir le système si la table est indexée. bref, tu vas effondrer le serveur lors des requêtes de selection, sans pour autant ne rien gagner sur l'exploitation générale

Reply

Marsh Posté le 02-07-2008 à 17:48:15

teste quand même ma solution sans sous-requête.

elle pourrait être performante, et au moins est standard...

bon, elle est performante dans une certaine mesure, vu qu'elle fait un produit cartésien sur log...

tout dépend ensuite de la façon donc mysql gère la clause having

Message édité par MagicBuzz le 02-07-2008 à 17:48:43

Reply

Marsh Posté le 02-07-2008 à 17:54:16

je refairais ptet les tests avec un index demain, juste pour voir

j'avais pas vu ta requête avec la having (même si elle était dans le post donné par avander)

Message édité par carborom le 02-07-2008 à 17:55:47

Reply

Marsh Posté le 02-07-2008 à 19:51:10

And the winner is ...

Code :

SELECT `MSG`, `USED_1`, `USED_2`, `USED_3`
FROM `msg`, `log` as l
WHERE `msg`.`MSG_ID` = l.`MSG_ID` AND
`TIME` = ( SELECT l1.`TIME`
FROM `log` as l1
WHERE l1.`MSG_ID` = l.`MSG_ID`
ORDER BY l1.`TIME` DESC
LIMIT 1)
ORDER BY LENGTH(`MSG`), `MSG`;

Couplé avec

Code :

ALTER TABLE `log` ADD INDEX (`TIME`, `MSG_ID`)

Requête sur 100.000 enregistrements dans la table `log` en moins de 2 sec.

J'ai pourtant testé avec tout ces index là (certains résultats m'ont paru bizarre quand même)

ADD INDEX (TIME)
ADD INDEX (MSG_ID)
ADD INDEX (TIME, MSG_ID)
ADD INDEX (MSG_ID, TIME)
ADD INDEX (MSG_ID), ADD INDEX (TIME)

Et la requête avec un HAVING met 30 sec sur 5000 enregistrements,
ceci, peu importe l'index sur la table `log`.

Message édité par carborom le 02-07-2008 à 19:55:39

Reply

Marsh Posté le 03-07-2008 à 12:22:40

assez logique que la requête avec le having soit très lente, étant donné qu'elle se paluche un produit cartésien

Reply

Marsh Posté le 03-07-2008 à 12:31:40

Mais c'est pas la plus lente

La requête "originale", celle qui me semblait la plus logique,
met entre 1 et 3 fois plus de temps -_-'

La requête en question (avec la sous-requête)

Code :

SELECT `MSG`, `USED_1`, `USED_2`, `USED_3`
FROM `msg` INNER JOIN `log` as l USING (`MSG_ID`)
WHERE `TIME` = (SELECT MAX(l1.`TIME`) FROM `log` AS l1 WHERE l1.`MSG_ID` = l.`MSG_ID`)
ORDER BY LENGTH(`MSG`), `MSG`;

Mais bon, je pense avoir trouvé le bon compromis

Message édité par carborom le 03-07-2008 à 12:32:21

Reply

Marsh Posté le 03-07-2008 à 12:36:27

normal, mysql gère assez mal les max().

en interne, il devrait aussi bien faire un limit + order by lorsqu'il n'y a pas de membres dans la clause group by
mais c'est clairement une optimisation qui est passée à la trappe !

le plus fun, c'est que la plus rapide et la plus lente sont strictement les mêmes à cette optimisation près ! il semble urgent que mysql pense à mettre en place cette optimisation

Message édité par MagicBuzz le 03-07-2008 à 12:37:16

Reply

Marsh Posté le

Reply

[SQL] une seule requête au lieu de deux...

Sujets relatifs:

Leave a Replay