Sous-Sous-Requête: Optimisation possible ?

**FMaz** · 02/04/2008, 06h42

Contexte:
Un système de Ticket: une table pour les tickets, une table pour les réponses aux tickets/sujets.

But de la requête:
Faire la liste de tout les tickets qui sont ouvert et sous le suivit d'un administrateur particulier (ici, le #15)

La table perso sert simplement à sortir le nom de l'utilisateur qui a posté le ticket.

En fait, le but est assez simple:
Sortir des informations sur les tickets ouvert et attribués à l'administrateur #15,
et afficher les informations suivantes au sujet de la réponse la plus récente:
- la date (r.`date` as rep_date)
- si la réponse provient d'un membre ou d'un administrateur (r.mjid as rep_mj)

Voici la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT m.*, p.nom as perso, r.`date` as rep_date, r.mjid as rep_mj
					FROM cc_ppa as m
					LEFT JOIN cc_perso as p ON (p.id = m.persoid) 
					LEFT JOIN (
						SELECT *
						FROM (
							SELECT *
							FROM cc_ppa_reponses
							ORDER BY `date` DESC
						)
						GROUP BY sujetid
					) as r ON (r.sujetid=m.id)
					WHERE	m.mjid=15
						AND m.statut="ouvert"
					ORDER BY m.`date` DESC;

Comme vous le constatez, elle contient 2 sous-requête imbriqués l'une dans l'autre. Le problème est celle qui est la plus profonde. En effet, elle fait la liste de TOUTES les réponses existantes.

Quand il y en aura 100000, ca ne sera pas tellement optimisé.
Ce que je voudrais en fait, c'est que le GROUP BY, plutôt que de prendre le premier résultat disponible, prenne la date la plus élevée.

Code des tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
CREATE TABLE IF NOT EXISTS `cc_ppa_reponses` (
  `id` int(12) NOT NULL auto_increment,
  `sujetid` int(12) NOT NULL,
  `mjid` int(12) NOT NULL COMMENT 'Réponse du perso = id 0',
  `date` int(10) NOT NULL,
  `msg` text NOT NULL,
  `notemj` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `sujetid` (`sujetid`,`date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;
 
--
-- Contenu de la table `cc_ppa_reponses`
--
 
INSERT INTO `cc_ppa_reponses` (`id`, `sujetid`, `mjid`, `date`, `msg`, `notemj`) VALUES
(2, 5, 1, 1186947459, '', 'blablabla'),
(3, 5, 1, 1186947495, 'Non, j\\''ai pas envie, pas ce soir, j\\''ai mal à la tête...', ''),
(4, 6, 1, 1186947785, 'Je sais pas, dsl.', '...'),
(5, 6, 1, 1186947797, 'Je sais pas, dsl.', ''),
(6, 6, 1, 1186947834, 'aucune idée', ''),
(7, 6, 1, 1186947866, 'Blablabla', ''),
(8, 6, 1, 1186948313, '\\"La marche du vertueux est semée d\\''obstacles que fait surgir sans fin l\\''oeuvre du malin.\r\nBénit soit-il, l\\''homme de bonne volonté, qui au nom de la charité se fait le berger des faibles qu\\''il guide dans la vallée des ombres des larmes et de la mort. [...]\r\nJ\\''abattrais alors mon bras sur les hordes impies qui pourchassent et réduisent à néant les brebis de Dieux.\r\nEt tu comprendra pourquoi mon nom est l\\''Eternel, quand sur toi s\\''abattra la colère du Tout Puissant.\\"', ''),
(9, 6, 1, 1186948318, '\\"La marche du vertueux est semée d\\''obstacles que fait surgir sans fin l\\''oeuvre du malin.\r\nBénit soit-il, l\\''homme de bonne volonté, qui au nom de la charité se fait le berger des faibles qu\\''il guide dans la vallée des ombres des larmes et de la mort. [...]\r\nJ\\''abattrais alors mon bras sur les hordes impies qui pourchassent et réduisent à néant les brebis de Dieux.\r\nEt tu comprendra pourquoi mon nom est l\\''Eternel, quand sur toi s\\''abattra la colère du Tout Puissant.\\"', ''),
(10, 6, 1, 1193424616, '', ''),
(11, 6, 1, 1203497049, '', ''),
(12, 7, 1, 1203497638, 'bla', 'bla'),
(13, 7, 1, 1203497945, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa', 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaavvv'),
(14, 8, 1, 1203498019, 'aaaaa', ''),
(15, 10, 1, 1203498244, 'aaaaa', 'aaaaa'),
(16, 9, 1, 1203498269, 'aaaaa', 'aaaaa'),
(17, 9, 1, 1203498272, 'aaaaa', 'aaaaa'),
(18, 11, 1, 1203498350, 'aaaaa', 'aaaaa'),
(19, 11, 1, 1203498384, 'aaaaa', 'aaaaa');
 
 
 
-- phpMyAdmin SQL Dump
-- version 2.11.2
-- http://www.phpmyadmin.net
--
-- Serveur: 127.0.0.1
-- Généré le : Mer 02 Avril 2008 à 00:37
-- Version du serveur: 5.0.32
-- Version de PHP: 5.2.0-8+etch10
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Base de données: `cybercity2034_v4`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `cc_ppa`
--
 
CREATE TABLE IF NOT EXISTS `cc_ppa` (
  `id` int(12) NOT NULL auto_increment,
  `persoid` int(12) NOT NULL,
  `type` varchar(10) NOT NULL,
  `date` int(10) NOT NULL,
  `mjid` int(12) NOT NULL default '0' COMMENT 'Si attribution == mj ID, si général == 0',
  `titre` tinytext NOT NULL,
  `msg` text NOT NULL,
  `lieu` varchar(150) NOT NULL,
  `pa` smallint(3) NOT NULL,
  `paMax` smallint(3) NOT NULL,
  `pv` smallint(3) NOT NULL,
  `pvMax` smallint(3) NOT NULL,
  `notemj` text NOT NULL,
  `statut` enum('ouvert','ferme') NOT NULL default 'ouvert',
  PRIMARY KEY  (`id`),
  KEY `parentid` (`mjid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
 
--
-- Contenu de la table `cc_ppa`
--
 
INSERT INTO `cc_ppa` (`id`, `persoid`, `type`, `date`, `mjid`, `titre`, `msg`, `lieu`, `pa`, `paMax`, `pv`, `pvMax`, `notemj`, `statut`) VALUES
(5, 48, 'ppa', 1186947221, 1, 'Jveut une F4 lol', 'jveut un fusil', 'A', 7, 99, 99, 99, '', 'ferme'),
(6, 5, 'question', 1186947678, 1, 'PPA', 'comment ca marche les ppas ?', 'A.pizza', 94, 99, 66, 99, '', 'ferme'),
(7, 5, 'ppa', 1203497451, 1, '**', 'blabla', 'A.pizza', 200, 400, 98, 98, '', 'ouvert'),
(8, 5, 'question', 1203497990, 0, 'aaaaa', 'aaaaaaaaaa', 'A.pizza', 200, 400, 98, 98, '', 'ouvert'),
(9, 48, 'plainte', 1203498205, 1, 'aaaaa', 'aaaaaaaaaa', 'A.pizza', 640, 990, 76, 99, '', 'ouvert'),
(10, 48, 'question', 1203498219, 11, 'aaaaa', 'aaaaaaaaaa', 'A.pizza', 640, 990, 76, 99, '', 'ouvert'),
(11, 48, 'quete', 1203498329, 1, 'aaaaa', 'aaaaa', 'A.pizza', 640, 990, 76, 99, '', 'ouvert');

**ced** · 02/04/2008, 09h13

Bonjour,

Pour n'avoir plus qu'une seule sous-requête

, ta requête peut aussi s'écrire comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT m.*, p.nom AS perso, c.`date` AS rep_date, c.mjid AS rep_mj
FROM cc_ppa_reponses c
LEFT JOIN cc_perso AS p ON (p.id = m.persoid)
LEFT JOIN cc_ppa m ON c.sujetid = m.id
WHERE (
  SELECT COUNT(*)
  FROM cc_ppa_reponses c2
  WHERE c.sujetid = c2.sujetid
  AND c.`date` < c2.`date`
) < 1
AND m.mjid=15
AND m.statut = 'ouvert'
ORDER BY m.`date` DESC

ced

**FMaz** · 02/04/2008, 12h41

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT m.*, p.nom AS perso, c.`date` AS rep_date, c.mjid AS rep_mj
FROM cc_ppa_reponses as c
LEFT JOIN cc_ppa as m ON (c.sujetid = m.id)
LEFT JOIN cc_perso AS p ON (p.id = m.persoid)
WHERE (
  SELECT COUNT(*)
  FROM cc_ppa_reponses c2
  WHERE c.sujetid = c2.sujetid
  AND c.`date` < c2.`date`
) < 1
AND m.mjid=15
AND m.statut = 'ouvert'
ORDER BY m.`date` DESC

(J'ai du l'adapter un peu: mettre left join cc_ppa avant left join cc_perso et rajouter 2 "as")

Wow, c'est ... tordu (?) comme logique.... mais ca marche

Concrètement, à quel point la requête est plus optimisée que la mienne ?

**SphynXz** · 02/04/2008, 13h12

ca sent fort les requète made in ced

(c'est pas du tout une critique je te rassure :p, c'est que je t'ai déjà vu poster ce genre de solution

)

selon moi, cette requète sera plus rapide du fait de l'absence d'order by & de group by

la sous requête ressort le nombre de date plus récente à celle que tu recherche. si elle n'en compte aucune, alors ta date est la plus récente, c'est pas tordu, c'est plutôt logique

, voire ce qu'il faut faire

**ced** · 02/04/2008, 13h42

Envoyé par SphynXz

ca sent fort les requète made in ced

(c'est pas du tout une critique je te rassure :p, c'est que je t'ai déjà vu poster ce genre de solution

)

Rendons à César ce qui est à César : ça n'est pas de moi (ah, un mythe s'effondre

).
C'est la façon d'implémenter la division relationnelle en SQL. Une fois qu'on a découvert ça et qu'on en a compris le fonctionnement, ça ouvre tout plein de perspectives... Ca permet de régler toutes les questions du genre : les X plus grands (ou récents, ou vieux, ou...) objet de chaque catégorie d'objet.
Il y a un très bon article là-dessus ici.

Pour les performances, je pense aussi que cette requête tournera plus vite (il n'y a qu'une seule sous-requête, avec un count(*) dedans, ce qui ne devrait pas être trop gourmand).

Enfin, pour le LIMIT... je crois bien que MySQL ne le permet pas dans les sous-requêtes (de mémoire).

**FMaz** · 02/04/2008, 13h50

LIMIT ne peut pas être utilisé dans une sous-requête introduite par In, All ou Any/Some

- Source: MySQL 5: Le guide complet (Micro application)

... donc ca passe avec les version récente de mysql, mais comme on l'a dit plus haut, c'est ridicule de placer un limit pour un count.

Peut-ont lui dire d'arrêter de compter passé 1 ?

**FMaz** · 02/04/2008, 13h20

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT m.*, p.nom AS perso, c.`date` AS rep_date, c.mjid AS rep_mj
FROM cc_ppa_reponses as c
LEFT JOIN cc_ppa as m ON (c.sujetid = m.id)
LEFT JOIN cc_perso AS p ON (p.id = m.persoid)
WHERE (
  SELECT COUNT(*)
  FROM cc_ppa_reponses c2
  WHERE c.sujetid = c2.sujetid
  AND c.`date` < c2.`date`
  LIMIT 1
) < 1
AND m.mjid=15
AND m.statut = 'ouvert'
ORDER BY m.`date` DESC

Et si on ajoute un LIMIT 1 dans la sous-requête, c'est une bonne idée ?

Et par "tordu", je voulais simplement dire qu'il ne m'étais pas venu à l'idée d'aborder le problème en commençant par la réponse, puis d'aller chercher le sujet.

La logique "naturelle" de pensée me dictait d'aller chercher le sujet AVANT sa réponse.

C'était pas une critique, loin de là, j'ai l'impression d'avoir ouvert les yeux en lisant la réponse tout simplement