Discussion :

[florent.hascher]

Bonjour,
J'aimerais savoir si une requete de type A JOIN B JOIN C where C = 'string'
avec
A environ 30 millions de lignes
B environ 1 million de lignes
C environ 2000 lignes
Est une requete qui de taille importante pour MySql
B est une table de liaison entre A et C.

Ma requete prend plusieurs minutes, avec index. Est ce normal?

Que faire pour l'accélérer de façon significative?

Merci

[CinePhil]

Tu peux analyser le plan d'exécution de la requête en faisant :

Code :

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

EXPLAIN -- suivi du texte de la requête

30 millions de lignes, ça commence à faire beaucoup mais ça dépend aussi des types de colonnes servant aux conditions de jointures et de la pertinence des index.

Il nous faudrait la requête réelle, la structure des tables et le résultat de l'EXPLAIN pour analyser tout ça.

[florent.hascher]

Voila un example de requete:

Code :

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

1
2
3
4
5
EXPLAIN select md.idCSN, md.idCSNfk, md.UISMILES, md.activityType, md.activity 
from ADS_schema.FK_MAIN_DATA_CSN md 
JOIN ADS_schema.FK_PROJECTCODE_CSN fkPC ON md.idCSN = fkPC.idCSN 
JOIN ADS_schema.PROJECT_CODE pc ON fkPC.idProjectCode = pc.idProjectCode 
WHERE (projectCode LIKE 'I-2007-003' )

Code :

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

1
2
3
4
5
 
id 	select_type 	table 	type 	possible_keys 		key 		key_len 	ref 				rows 	Extra
1 	SIMPLE 		pc 	range 	PRIMARY,projectCode 	projectCode 	66 		NULL				1 	Using where; Using index
1 	SIMPLE 		fkPC 	ref 	CSN,idProjectCode 	idProjectCode 	4 		ADS_schema.pc.idProjectCode 	120 	Using where
1 	SIMPLE 		md 	ref 	idCSN 			idCSN 		5 		ADS_schema.fkPC.idCSN 		12 	Using where

projectCode est de type varchar
idProjectCode et idCSN sont des int.

En gros les tables sont les suivantes:

Code :

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

1
2
3
4
5
6
7
 
FK_MAIN_DATA_CSN      FK_PROJECTCODE_CSN          PROJECT_CODE 
idCSN (pk)              idCSN  (pk)                     idProjectCode (pk) 
idCSNfk                 idProjectCode (pk)               projectCode  
UISMILES
activityType (pk)
activity

Dis moi s'il te faut plus d'infos.

Il y a t-il un problème dans cette requete?
Est ce que les performances serait meilleures avec PostGre ?

Merci

[CinePhil]

Il manque la structure des tables, résultats de SHOW CREATE TABLE la_table
On peut cependant faire une remarque : LIKE sans caractère générique est équivalent à = alors autant utiliser = !
Ensuite, il est inutile de mettre la condition du WHERE entre parenthèses.

Pour le reste, la requête est correctement écrite et le nombre de lignes examinées est faible. cette requête devrait s'exécuter très rapidement.

[florent.hascher]

Est ce qu'il y aurait une grande difförence de vitesse en utilisant une machine avec beaucoup de RAM (32 par example au lieu de 4GB actuellement)?

[florent.hascher]

Voila la structure 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
FK_MAIN_DATA_CSN 	CREATE TABLE `FK_MAIN_DATA_CSN` (
 `idCSN` int(11) DEFAULT NULL,
 `CSN` varchar(10) NOT NULL,
 `activityType` varchar(127) DEFAULT NULL,
 `activity` double NOT NULL,
 `UISMILES` varchar(4000) DEFAULT NULL,
 `amount` double DEFAULT NULL,
 `regDate` date DEFAULT NULL,
 `lastWrittenDate` date DEFAULT NULL,
 PRIMARY KEY (`activityType`,`idCSNfk`),
 KEY `CSN` (`idCSNfk`),
 KEY `activityType` (`activityType`),
 KEY `idCSN` (`idCSN`),
 KEY `activity` (`activity`),
 KEY `UISMILES` (`UISMILES`(100))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
FK_PROJECTCODE_CSN 	CREATE TABLE `FK_PROJECTCODE_CSN` (
 `idProjectCode` int(11) NOT NULL,
 `idCSN` int(11) NOT NULL,
 PRIMARY KEY (`idCSN`,`idProjectCode`),
 KEY `idCSN` (`idCSN`),
 KEY `idProjectCode` (`idProjectCode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
PROJECT_CODE 	CREATE TABLE `PROJECT_CODE` (
 `idProjectCode` bigint(20) NOT NULL AUTO_INCREMENT,
 `projectCode` varchar(64) NOT NULL,
 `lastWritenDate` date NOT NULL,
 PRIMARY KEY (`idProjectCode`),
 UNIQUE KEY `projectCode` (`projectCode`)
) ENGINE=InnoDB AUTO_INCREMENT=214745 DEFAULT CHARSET=latin1

ouai, j'ai fini par mettre des index un peu partout ...

[CinePhil]

Euh... il y a des trucs bizarres !

Code :

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

1
2
PRIMARY KEY (`activityType`,`idCSNfk`),
KEY `CSN` (`idCSNfk`),

Je ne vois pas la colonne `idCSNfk`dans la liste des colonnes mais seulement une idCSN !

Encore un bug de MySQL ?

Je pense que la clé primaire devrait seulement être idCSN non ?
En tout cas, mettre une colonne de type VARCHAR(127) dans la clé primaire est contre performant avec cette grosse table.

Code :

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

1
2
3
PRIMARY KEY (`idCSN`,`idProjectCode`),
 KEY `idCSN` (`idCSN`),
 KEY `idProjectCode` (`idProjectCode`)

Le deuxième index sur idCSN est inutile car la colonne est déjà indexée en tant que première colonne de la clé primaire.

Code :

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

1
2
3
4
5
FK_PROJECTCODE_CSN 	CREATE TABLE `FK_PROJECTCODE_CSN` (
 `idProjectCode` int(11) NOT NULL,
 
PROJECT_CODE 	CREATE TABLE `PROJECT_CODE` (
 `idProjectCode` bigint(20) NOT NULL AUTO_INCREMENT,

Les deux colonnes idProjectCode devraient être exactement du même type puisque l'une est normalement une clé étrangère référençant l'autre, même si ici tu n'as pas déclaré les contraintes de clés étrangères.

Est ce qu'il y aurait une grande difförence de vitesse en utilisant une machine avec beaucoup de RAM (32 par example au lieu de 4GB actuellement)?

4 GB c'est déjà pas mal mais si tu as la possibilité d'augmenter c'est mieux car un SGBD travaille toujours en mémoire.

[florent.hascher]

OK merci, je vais faire les corrections.

En testant un peu je me suis rendu compte que si je renvois que le premier champ 'md.idCSN' la requete ne prend que quelques secondes mais en retournant tout les champs le temps est multiplié par 10-15.

Est ce inévitable?

[CinePhil]

C'est assez normal, notamment à cause de la colonne en VARCHAR(4000) !
Cela fait quand même potentiellement 4000 octets par ligne à extraire du disque et a envoyer sur le réseau. Avec 4 Go de RAM, le SGBD ne peut donc traiter en mémoire que moins de 1000 lignes.
Cette histoire de SMILES ne me semble vraiment pas optimale ! Ne serait-il pas possible de modéliser ces SMILES ? Ce ne sont après tout que des assemblages d'éléments chimiques !

[Fred_34]

Cela fait quand même potentiellement 4000 octets par ligne à extraire du disque et a envoyer sur le réseau. Avec 4 Go de RAM, le SGBD ne peut donc traiter en mémoire que moins de 1000 lignes.

1 million...

[CinePhil]

Action : mettre à jour le logiciel de calcul mental dans mon vieux cerveau !

[florent.hascher]

Merci pour tes réponses.
Qu'est ce tu veux dire par modéliser ces SMILES?

En général le SMILES code ne dépasse pas 100 caractères et le plus long actuellement en base de données fait 633 caractères.

Est ce le nombre de caractère 'réelement' utilisé qui compte ou le nombre de caractère que le colonne peut contenir?

[CinePhil]

Qu'est ce tu veux dire par modéliser ces SMILES?

Ben je ne suis pas spécialiste de la question, ni chimiste, ni biochimiste, ni pharmacien mais au départ tu as ce genre d'association non ?
molecule -1,n----contenir----0,n- atome

Ensuite, au risque d'employer de mauvais terme vu que ça fait plus de trente ans que je n'ai pas eu de cours de chimie, s'il faut enregistrer le nombre de liaisons covalentes ou un ordre dans le chaînage des atomes dans la molécule, il faut peaufiner le modèle mais ça doit être faisable.
Et comme toutes ces liaisons seront enregistrées à l'aide d'identifiants entiers, les requêtes devraient être beaucoup plus rapide que de chercher une séquence dans une chaîne comprenant plusieurs centaines de caractères, ce qui n'est pas indexable.

En général le SMILES code ne dépasse pas 100 caractères et le plus long actuellement en base de données fait 633 caractères.

Alors pourquoi avoir mis un VARCHAR (4000) ?

Est ce le nombre de caractère 'réelement' utilisé qui compte ou le nombre de caractère que le colonne peut contenir?

Un peu des deux. Je ne sais pas précisément comment ça se passe chez MySQL mais si j'ai correctement retenu ce qui m'a été dit lors d'une formation Oracle récemment, le SGBD réserve de la place en fonction de la taille maxi de la colonne.
Il faut essayer d'ajuster les types et tailles des colonnes au plus près de la réalité des données qu'on va stocker.

[tse_jc]

A propos de modéliser les SMILES cela rejoint ce que j'ai déjà évoqué ici

Bonjour,

Sur les 10 600 monomères de votre exemple, combien sont identiques? combien il y en a-t-il de différents?
Les contraintes spécifiques nécessitent la plupart du temps des modèles spécifiques.

Jc

[florent.hascher]

J'ai lu sur le site de MySql:
"Utilisez la tables de type HEAP pour accélérer les traitements au maximum"

Puis je convertir mes tables InnoDB vers HEAP facilement et est ce que cela vaut le coup?

J'avais essayé en fait il y a quelques temps en important depuis phpMyAdmin un fichier .sql d'environ 2.7 millions de lignes, mais j'ai annulé après quelques heures...

Merci

[CinePhil]

HEAP est, semble t-il, le moteur pour les tables qu'on laisse en mémoire vive.

Ça va pour des petites tables mais pas pour un gros volume.

J'avais essayé en fait il y a quelques temps en important depuis phpMyAdmin un fichier .sql d'environ 2.7 millions de lignes, mais j'ai annulé après quelques heures...

Pour importer de gros volumes de données, il faut utiliser une requête LOAD DATA INFILE de préférence en mode console car avec phpMyAdmin on arrive assez vite au timeout de php.
Idem pour importer un fichier SQL : un coup de

Code :

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

mysql -u user -pmotpasse bdd < fichier.sql

en mode console est plus sûr que d'utiliser phpMyAdmin si la BDD commence à être un peu grosse.

[florent.hascher]

ok, merci pour ces réponses