Union multiple --> out of ressource

**senacle** · 27/01/2011, 19h02

Bonjour,

Je vous donne un modèle simplifié de ce que j'ai, mais c'est pour être plus clair dans l'explication.

J'ai une table A :

Code :

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

1
2
3
4
Combinaison    Valeur
1-2-3-5           10,5
5-6-9-10          2,56
...

Dans combinaison, j'ai, au fur et à mesure que la table se remplira, les combinaisons de 4 nombres parmi 10.

Maintenant, je veux sortir un résultat du genre :

Code :

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

SELECT '1-2-3-4-5', COUNT(Combinaison) NB, MIN(Valeur) MINIMUM, COUNT( * )*AVG( Valeur ) FROM A WHERE Combinaison IN ('1-2-3-4', '1-2-3-5, '1-2-4-5', '1-3.-4-5', '2-3-4-5')

Quand il n'y a qu'une requête, c'est OK, ça va vite.

Avec deux requêtes :

Code :

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

1
2
3
SELECT '1-2-3-4-5', COUNT(Combinaison) NB, MIN(Valeur) MINIMUM, COUNT( * )*AVG( Valeur ) FROM A WHERE Combinaison IN ('1-2-3-4', '1-2-3-5, '1-2-4-5', '1-3-4-5', '2-3-4-5')
UNION
SELECT '1-2-3-4-6', COUNT(Combinaison) NB, MIN(Valeur) MINIMUM, COUNT( * )*AVG( Valeur ) FROM A WHERE Combinaison IN ('1-2-3-4', '1-2-3-6, '1-2-4-6', '1-3-4-6', '2-3-4-6')

Là, ça va encore.

Mon problème, c'est que j'arrive en fait à plusieurs milliers de requêtes à mettre en UNION.
Et là, vous vous en doutez, mysql dit stop : Out of resources when opening file A.

Y a-t-il une solution avec MySQL ?

Sinon, comme je suis en php, je pense mettre le résultat de chaque requête dans une ligne d'un tableau que je trierai ensuite.

**skuatamad** · 27/01/2011, 20h38

UNION fait un DISTINCT, UNION ALL est plus approprié.

Quel est le résultat souhaité ? Dans le IN tu places la totalité des combinaisons de 4 chiffres ?
Tu t'es trompé à priori en utilisant '1-2-3-4-5' dans le SELECT, il faudrait mieux expliquer la relation entre la combinaison sélectionnée en dur dans le SELECT et la liste à placer dans le IN.

Sinon, comme je suis en php, je pense mettre le résultat de chaque requête dans une ligne d'un tableau que je trierai ensuite.

C'est généralement une mauvaise idée, mais pour l'instant je n'ai pas bien compris ce que tu demandes à ta base.

**senacle** · 27/01/2011, 21h21

Envoyé par skuatamad

Quel est le résultat souhaité ? Dans le IN tu places la totalité des combinaisons de 4 chiffres ?
Tu t'es trompé à priori en utilisant '1-2-3-4-5' dans le SELECT, il faudrait mieux expliquer la relation entre la combinaison sélectionnée en dur dans le SELECT et la liste à placer dans le IN.

Oui, la combinaison des 4 chiffres parmi ceux du SELECT.
Donc
pour '1-2-3-4-5', j'aurai dans le IN '1-2-3-4', '1-2-3-5', 1-2-4-5', '1-3-4-5', '2-3-4-5'
pour '1-2-3-4-6', j'aurai dans le IN '1-2-3-4', '1-2-3-6', 1-2-4-6', '1-3-4-6', '2-3-4-6'
etc...

(j'ai mis à jour mon premier post où le IN était incorrect)

**skuatamad** · 27/01/2011, 23h47

Qu'est ce que ça donne avec UNIONN ALL ? Mais je n'attends pas de miracle sur plus de 1000 UNION ALL...

As tu la possibilité de faire évoluer ton schéma ?
Si oui créer une table contenant la totalité des combinaisons sur 5 chiffres et une table associant les cominaisons sur 5 chiffres et les combinaisons sur 4 chiffres permettrait de faire des jointures à la place des 1000+ UNION

Ci -dessous les tables en questions, je les ai créées sur oracle (en virant juste le from dual spécifique à oracle) donc à l'erreur de syntaxe près (j'espère que ça passe)

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
create table combinaison_5 as (
  select '1-2-3-4-5' as comb_5 union all 
  select '1-2-3-4-6' from dual
);
 
create table combinaison_5_4 as (
  select '1-2-3-4-5' as comb_5,'1-2-3-4' as combinaison union all
  select '1-2-3-4-5', '1-2-3-5' union all
  select '1-2-3-4-5', '1-2-4-5' union all
  select '1-2-3-4-5', '1-3-4-5' union all
  select '1-2-3-4-5', '2-3-4-5' union all
  select '1-2-3-4-6', '1-2-3-4' union all
  select '1-2-3-4-6', '1-2-3-6' union all
  select '1-2-3-4-6', '1-2-4-6' union all
  select '1-2-3-4-6', '1-3-4-6' union all
  select '1-2-3-4-6', '2-3-4-6'
);
 
create table combinaison as (
  select '1-2-3-5' as combinaison, 10.5 as valeur union all
  select '1-2-3-6', 2.56 from dual
);

Et la requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT c_5.comb_5
       , COUNT(c.Combinaison) NB
       , MIN(c.Valeur) MINIMUM
       , COUNT(*)*AVG( c.Valeur ) 
FROM combinaison_5 c_5
JOIN combinaison_5_4 c_5_4 ON c_5.comb_5 = c_5_4.comb_5
JOIN combinaison c ON c_5_4.combinaison = c.combinaison
group by c_5.comb_5

Les tables, vite fait nommées combinaison_5 et combinaison_5_4, n'ont à priori pas à être maintenu par l'appli, il faut les créer directement avec toutes les combinaisons possibles... je sais c'est chiant !
J'espère que la colonne combinaison dans ton exemple est unique (et toujours correctement triée).
Sinon les résultats des count(*) notamment seront à adapter, peut être avec des DISTINCT, à vérifier...

Peux tu faire un test sur quelques combinaisons et donner ton retour stp ?

Evidemment pour faire plus propre il faut des PK/FK, peut être avec un ID auto incrémenté.

**senacle** · 31/01/2011, 21h01

L'idée de créer en base les tables avec toutes les combinaisons possibles est effectivement une très bonne solution.

J'ai créé 3 tables :

COMB_P_N --------1,n-------- CONTIENT --------1,n-------- COMB_4_P

Dans COMB_P_N, il y a les combinaisons :
de 5 parmi 10, 5 parmi 11, ... 5 parmi 22 (1-2-3-4-5, 1-2-3-4-6, ..., 18-19-20-21-22)
de 6 parmi 10, 6 parmi 11, ... 6 parmi 22 (1-2-3-4-5-6, 1-2-3-4-7, ..., 17-18-19-20-21-22)
de 7 parmi 10, 7 parmi 11, ... 7 parmi 22 (1-2-3-4-5-6-7, 1-2-3-4-6-8, ..., 17-18-19-20-21-22)
(au total, 271 491 combinaisons)

Dans COMB_4_P, il y a les combinaisons de 4 parmi 22 (1-2-3-4, 1-2-3-5, ..., 19-20-21-22)
(au total, 7 315 combinaisons)

Dans CONTIENT, on fait le lien entre les deux tables :
pour 1-2-3-4-5 dans COMB_P_N, on a 1-2-3-4, 1-2-3-5, 1-2-4-5, 1-3-4-5 et 2-3-4-5 dans COMB_4_P
Dans CONTIENT, il y a 7 219 905 enregistrements.

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
 
CREATE TABLE IF NOT EXISTS `COMB_4_P` (
  `ID_COMB_4_P` int(11) NOT NULL AUTO_INCREMENT,
  `COMB_4` varchar(40) NOT NULL,
  `PARMI_P` int(11) NOT NULL,
  PRIMARY KEY (`ID_COMB_4_P`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
--
-- Structure de la table `COMB_P_N`
--
 
CREATE TABLE IF NOT EXISTS `COMB_P_N` (
  `ID_COMB_P_N` int(11) NOT NULL AUTO_INCREMENT,
  `COMB_P` varchar(40) NOT NULL,
  `PARMI_N` int(11) NOT NULL,
  `P` tinyint(4) NOT NULL,
  PRIMARY KEY (`ID_COMB_P_N`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
 
-- --------------------------------------------------------
--
-- Structure de la table `CONTIENT`
--
 
CREATE TABLE IF NOT EXISTS `CONTIENT` (
  `ID_COMB_P_N` int(11) NOT NULL DEFAULT '0',
  `ID_COMB_4_P` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID_COMB_P_N`,`ID_COMB_4_P`),
  KEY `FK_CONTIENT_ID_COMB_4_P` (`ID_COMB_4_P`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

La requête devient alors très simple et s'exécute très vite.
Par exemple, si je veux faire une requête portant sur les combinaisons de 5 parmi 10 jusqu'à 5 parmi 16 :

Code :

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

1
2
3
4
5
6
7
8
SELECT COMB_P, COUNT(COMB_P) NB, MIN(MONTANT) MINIMUM, AVG( MONTANT ) MOYENNE, MAX(MONTANT) MAXIMUM, SUM(MONTANT) SOMME
FROM MONTANT
JOIN COMB_4_P ON MONTANT.ID_COMB_4_P = COMB_4_P.ID_COMB_4_P
JOIN CONTIENT ON COMB_4_P.ID_COMB_4_P = CONTIENT.ID_COMB_4_P
JOIN COMB_P_N ON CONTIENT.ID_COMB_P_N = COMB_P_N.ID_COMB_P_N
WHERE PARMI_N <= 16 AND P = 5
GROUP BY COMB_P

Pour remplir les tables, voici un script php :

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
 
<?php
	function partition($index, $p, $n, $ordered, $liste) {
		if ($index>=$p) {// la liste est construite -> FIN
			$comb = implode("-", $liste);
			$existe = "SELECT COMB_P FROM COMB_P_N WHERE COMB_P = '".$comb."'";
			$existe = mysql_query($existe);
			if (mysql_num_rows($existe) == 0) {
				$query = "INSERT INTO COMB_P_N (COMB_P, PARMI_N, P) VALUES ('".$comb."', ".$n.", ".$p.")";
				$result = mysql_query($query);
			}
			return;
		}
		// ajoute un nouvel element candidat dans la liste
		// - sans ordre -> candidat: tous les elements
		// - avec ordre -> candidat: seulement les elements supérieurs au précédant
		$start=1;
		if ($ordered && $index>0) {
			$start = $liste[$index-1]+1;
		}
		for($i=$start;$i<=$n;$i++) {
			$liste[$index] = $i;
			partition($index+1, $p, $n, $ordered, $liste);
		}
	}
 
	function partition2($index, $p, $n, $liste, $liste2, $id_comb) {
		if ($index>=$p) {// la liste est construite -> FIN
			$comb = implode("-", $liste);
			$existe = "SELECT ID_COMB_4_P, COMB_4 FROM COMB_4_P WHERE COMB_4 = '".$comb."'";
			$existe = mysql_query($existe);
			if (mysql_num_rows($existe) == 0) {
				//$query = "INSERT INTO COMB_".$n."_".$p." (ID_COMB_".$n.", COMB_".$n."_".$p.") VALUES (".$id_comb.", '".implode("-", $liste)."');";
				$query = "INSERT INTO COMB_4_P (COMB_4, PARMI_P) VALUES ('".$comb."', ".$n.");";
				$result = mysql_query($query);
				$last_id = "SELECT LAST_INSERT_ID() ID;";
				$last_id = mysql_query($last_id);
				$last_id = mysql_fetch_array($last_id);
				$result = mysql_query("INSERT INTO CONTIENT (ID_COMB_P_N, ID_COMB_4_P) VALUES (".$id_comb.", ".$last_id['ID'].")");
			} else {
				$existe = mysql_fetch_array($existe);
				$result = mysql_query("INSERT INTO CONTIENT (ID_COMB_P_N, ID_COMB_4_P) VALUES (".$id_comb.", ".$existe['ID_COMB_4_P'].")");
			}
			return;
		}
		// ajoute un nouvel element candidat dans la liste
		// - sans ordre -> candidat: tous les elements
		// - avec ordre -> candidat: seulement les elements supérieurs au précédant
		$start=0;
		if ($index>0) {
			$start = array_search($liste[$index-1], $liste2) + 1;
		}
		for($i=$start;$i<$n;$i++) {
			$liste[$index] = $liste2[$i];
			partition2($index+1, $p, $n, $liste, $liste2, $id_comb);
		}
	}
 
	set_time_limit (0);
	include "connexion_bdd.php";
/************************************/
/*			TABLE COMB_P_N			*/
/************************************/
/* 30 minutes environ pour le remplissage */
	for ($j=5;$j<8;$j++) {
		for ($i=10;$i<23;$i++) {
			partition(0, $j, $i, true, array());
		}
	}
/************************************/
/*	TABLES COMB_4_P et CONTIENT		*/
/************************************/
/* 20 minutes environ pour le remplissage */
	$result = mysql_query("SELECT ID_COMB_P_N ID_COMB, COMB_P COMB FROM COMB_P_N");
	while ($row = mysql_fetch_array($result)) {
		$array = explode('-', $row['COMB']);
		partition2(0, 4, count($array), array(), $array, $row['ID_COMB']);
	}
	mysql_close($conn);
?>

Union multiple --> out of ressource

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager