Requête SQL avec 2 count [Résolu]

[la_chouette]

Bonjour,

Je cherche à parcourir une table et faire 2 count dans une autre le tout en 1 requête, exemple:



Voici la structure des deux tables:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE TABLE `terms` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `term` varchar(50) NOT NULL DEFAULT '',
  `status` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `term` (`term`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
CREATE TABLE `suggests` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `suggest` varchar(50) NOT NULL DEFAULT '',
  `termid` smallint(6) UNSIGNED NOT NULL DEFAULT '0',
  `status` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `suggest` (`suggest`),
  KEY `termid` (`termid`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

et la requête utilisée pour le moment:

Code :

1
2
3
4
5
6
7
8
9
 
SELECT t.id, t.term, tmp_todo.nb_todo, tmp_done.nb_done
	FROM terms t
LEFT JOIN (SELECT s.termid, count(s.STATUS) AS nb_todo FROM suggests s WHERE s.STATUS = '0' GROUP BY s.termid) tmp_todo ON t.id = tmp_todo.termid
LEFT JOIN (SELECT s.termid, count(s.STATUS) AS nb_done FROM suggests s WHERE s.STATUS = '1' GROUP BY s.termid) tmp_done ON t.id = tmp_done.termid
	WHERE t.STATUS = '0'
GROUP BY t.id
	ORDER BY t.term ASC
LIMIT 10

Quand j'utilise cette requête dans une boucle while, ceci boucle sur le premier résultat uniquement...

Merci d'avance pour votre aide, cordialement

[punkoff]

Bonjour,

Code :

1
2
3
4
5
6
7
8
 
SELECT t.id, t.term, 
   count(*) - sum(s.STATUS) AS "SUGGEST / STATUS 0",
   sum(STATUS) AS "SUGGEST / STATUS 1"
FROM terms t
INNER JOIN suggest s ON s.term_id = t.id
WHERE t.STATUS = 0
GROUP BY t.id, t.term

[la_chouette]

Bonjour,

merci pour votre aide.

J'ai modifier la requête légèrement mais ne n'obtiens aucun résultat:

Code :

1
2
3
4
5
6
7
8
SELECT t.id, t.term, 
   count(*) - sum(s.STATUS) AS "nb_todo / status 0",
   sum(s.STATUS) AS "nb_done / status 1"
FROM terms t
	INNER JOIN suggests s ON s.termid = t.id
WHERE t.STATUS = 0
	GROUP BY t.id, t.term
ORDER BY t.term ASC LIMIT 10

[dehorter olivier]

voici ce que je te suggère :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT Selection.Id,
       Selection.TERM,
       SUM(
       CASE
         WHEN Suggests.STATUS = 0 THEN
             1
       END) AS Suggest_Status_0,
       SUM(
       CASE
         WHEN Suggests.STATUS = 1 THEN
             1
       END) AS Suggest_Status_1
  FROM (SELECT Terms.Id,
               Terms."TERM"
          FROM Terms
          WHERE Terms.STATUS = 0) AS Selection
    INNER JOIN Suggests ON (Suggests.Termid = Selection.Id)
  GROUP BY Selection.Id, Selection.TERM

[la_chouette]

Bonjour,

merci également pour votre aide.

Votre requête proposé ne retourne aucun résultat non plus...

Code :

1
2
3
4
5
6
7
8
9
 
SELECT selection.id,
       selection.term,
       SUM(CASE WHEN suggests.STATUS = 0 THEN 1 END) AS suggest_status_0,
       SUM( CASE WHEN suggests.STATUS = 1 THEN 1 END) AS suggest_status_1
FROM (SELECT terms.id, terms.term FROM terms WHERE terms.STATUS = 0) AS selection
	INNER JOIN suggests ON (suggests.termid = selection.id)
GROUP BY selection.id, selection.term
ORDER BY selection.term ASC LIMIT 10

[dehorter olivier]

j'ai créé les tables et données, et le résultat de la requête est cohérent avec votre exemple.

et sans le LIMIT ?

Battu par punkoff (et qui fonctionne aussi chez moi) et qui est meilleur en terme d'optimisation

[la_chouette]

Je vous assure que chez moi la requête suivante ne donne rien:

Code :

1
2
3
4
5
6
7
8
SELECT t.id, t.term, 
   count(*) - sum(s.STATUS) AS "nb_todo / status 0",
   sum(s.STATUS) AS "nb_done / status 1"
FROM terms t
	INNER JOIN suggests s ON s.termid = t.id
WHERE t.STATUS = 0
	GROUP BY t.id, t.term
ORDER BY t.term ASC

Edit: voici mes deux tables chez moi

Code :

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
CREATE TABLE IF NOT EXISTS `suggests` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `suggest` varchar(50) NOT NULL DEFAULT '',
  `termid` smallint(6) UNSIGNED NOT NULL DEFAULT '0',
  `status` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `suggest` (`suggest`),
  KEY `termid` (`termid`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
 
INSERT INTO `suggests` (`id`, `suggest`, `termid`, `status`) VALUES
(1, 'bateau a', 2, '0'),
(2, 'bateau a vendre', 2, '1'),
(3, 'bateau amorceur', 2, '0'),
(4, 'bateau a vapeur', 2, '1'),
(5, 'maison algerie', 5, '0'),
(6, 'bateau a voile', 2, '0'),
(7, 'bateau a moteur', 2, '0'),
(8, 'maison annecy', 5, '1'),
(9, 'bateau antares', 2, '0'),
(10, 'bateau arcachon', 2, '0');
 
 
CREATE TABLE IF NOT EXISTS `terms` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `term` varchar(50) NOT NULL DEFAULT '',
  `status` enum('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `term` (`term`),
  KEY `status` (`status`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 
 
INSERT INTO `terms` (`id`, `term`, `status`) VALUES
(2, 'bateau', '0'),
(5, 'maison', '0');

[dehorter olivier]

trouve

c'est la colonne status de la table SUGGESTS est un alphanumerique (j'ai mis un entier).

Soit changer le type de colonnes en lui indiquant que ce sont des numériques [je ne connais pas MYSQL, mais j'imagine possible] :

Code :

`status` enum(0,1) NOT NULL DEFAULT 0,

Ou modifier ma requête pour changer les CASEs :

Code :

SUM(CASE WHEN suggests.STATUS = '0' THEN 1 END) AS suggest_status_0,

[la_chouette]

Cela ne fonctionne toujours pas chez moi, je ne comprends pas pourquoi !

Battu par punkoff (et qui fonctionne aussi chez moi) et qui est meilleur en terme d'optimisation

Oui je suis d'accord aussi mais dommage qu'elle ne retourne aucun résultat....

[la_chouette]

Bonsoir,

Je viens de trouver cette syntaxe SQL qui retourne un résultat correcte mais qui me semble pas du tout optimisé:

Code :

1
2
3
4
5
6
7
SELECT t.id, t.term,
    COUNT(CASE WHEN suggests.STATUS = '0' THEN 1 END) AS nb_todo,
    COUNT(CASE WHEN suggests.STATUS = '1' THEN 1 END) AS nb_done
FROM terms t
	INNER JOIN suggests ON (suggests.termid = t.id)
	WHERE t.STATUS = '0'
GROUP BY t.id ORDER BY t.term ASC LIMIT 10

[punkoff]

Essayez aussi avec ceci dans la clause select :

Code :

1
2
3
4
 
SELECT t.id, t.term,
    SUM(CASE WHEN suggests.STATUS = '0' THEN 1 else 0 END) AS nb_todo,
    SUM(CASE WHEN suggests.STATUS = '1' THEN 1 else 0 END) AS nb_done

Ensuite, essayez avec les index suivant :

Code :

1
2
3
 
CREATE INDEX idx_sug_test ON suggests (termid, STATUS);
CREATE INDEX idx_ter_test ON terms (STATUS, id);

Et comparer les temps de réponse avec 1 des 2 index, les 2, la 1ere requete, la 2eme etc.


edit : oula, j'ai loupé un truc, vous fait un group by sur une seule colonne uniquement !

réécrivez votre requête ainsi :

Code :

1
2
3
4
5
6
7
8
9
 
SELECT t.id, t.term,
    COUNT(CASE WHEN suggests.STATUS = '0' THEN 1 END) AS nb_todo,
    COUNT(CASE WHEN suggests.STATUS = '1' THEN 1 END) AS nb_done
FROM terms t
	INNER JOIN suggests ON (suggests.termid = t.id)
	WHERE t.STATUS = '0'
GROUP BY t.id, t.term
ORDER BY t.term ASC LIMIT 10

Ensuite est-ce que fonctionnellement l'order by et le limit sont util ? sinon essayez sans.

[la_chouette]

Merci pour votre aide, en faite je me suis aperçu que si un term ne contient pas de suggestions associées avec le statut 0 ou 1, alors il n'est pas retourné comme résultat.

édit:

Code :

INNER JOIN suggests ON (suggests.termid = t.id)

à la place

Code :

LEFT JOIN suggests ON (suggests.termid = t.id)

[dehorter olivier]

Je ne comprends pas ! Avec le INNER cela doit fonctionner...

as-tu essayé sans les opérations de regroupement ?

Code :

1
2
3
4
SELECT t.id, t.term
FROM terms t
	INNER JOIN suggests ON (suggests.termid = t.id)
	WHERE t.STATUS = '0'

ou

Code :

1
2
3
SELECT t.id, t.term
FROM terms t
	INNER JOIN suggests ON (suggests.termid = t.id AND  t.STATUS = '0')

[la_chouette]

Bonjour,

Cette requête fonctionne parfaitement, mais je doute de son optimisation:

Code :

1
2
3
4
5
6
7
8
SELECT t.id, t.term, 
	count(CASE WHEN suggests.STATUS = '0' THEN 1 END) AS nb_todo, 
	count(CASE WHEN suggests.STATUS = '1' THEN 1 END) AS nb_done 
FROM terms t 
	LEFT JOIN suggests ON (suggests.termid = t.id) 
WHERE t.STATUS = '0' 
	GROUP BY t.id 
	ORDER BY t.term ASC

Cette requête sera exécutée près de 70 000 fois dans des boucles while...

J'ai essayé également de nouveau de reprendre l'idée de punkoff mais je sèche ...

[dehorter olivier]

As-tu essayé la requête INNER sans les regroupements ?

Pourquoi n'essayes-tu pas de changer le type de la colonne STATUS en type numérique (ce qui te permet d'utiliser la première requête de PunkOff) ?


Il y a des papiers virulents sur l'optimisation des requêtes externes dans MySQL par SQLPro .... pour résumer :

[la_chouette]

As tu essayer la requête INNER sans les regroupements ?

Oui j'ai essayez, mais cela ne me retourne pas tout les term si aucune suggestion ne lui est associée.

Pourquoi n'essayes tu pas de changer le type de la colonne STATUS en type numérique (ce qui te permet d'utiliser la première requête de PunkOff) ?

Ah, je n'avais pas compris que ton conseil était pour la requête de PunkOff (je teste...)

Il y a des papiers virulents sur l'optimisation des requêtes externes dans MySQL par SQLPro .... pour résumer

Oui, la page est justement ouverte dans un autre onglet, je lit pour essayez de mettre en pratique...

En tout cas merci, pour l'aide apportée !

[punkoff]

Envoyé par la_chouette

Bonjour,

Cette requête fonctionne parfaitement, mais je doute de son optimisation:

Code :

Cette requête sera exécutée près de 70 000 fois dans des boucles while...

?????????? pour quelle raison ?

[la_chouette]

Bonjour,

Parce que je fait un traitement sur chaque term contenu dans la table terms, je doit donc extraire les infos un à un afin de savoir si celui-ci à été traité ou pas...

édit: sinon la requête est légèrement plus rapide que la mienne:

Code :

1
2
3
4
5
6
7
8
SELECT t.id, t.term, 
   count(s.STATUS) - sum(s.STATUS) AS nb_todo,
   sum(s.STATUS) AS nb_done
FROM terms t
	LEFT JOIN suggests s ON s.termid = t.id
WHERE t.STATUS = '0' 
	GROUP BY t.id 
	ORDER BY t.term ASC

[dehorter olivier]

Envoyé par la_chouette

Oui j'ai essayez, mais cela ne me retourne pas tout les term si aucune suggestion ne lui est associée.

Que veux tu dire pas "si aucune suggestion ne lui est associée"


Je suis un peu comme PunkOff ?????????????????

[la_chouette]

En faite si le term voiture à bien le statut 0 mais aucune suggestion dans la table suggests lui étant associé via id termid n'est trouver, alors le term ne sera pas retourner comme résultat...

Pour simplifié:
J'ai besoin d'extraire tout les term de la table terms en comptant séparément le nombre de suggestion lui étant associée dans la table suggests avec le statut 0 et 1...