Bonjour,
j'ai un petit souci d'optimisation de ma bdd. Mon probleme : j'ai une table "actuel" comportant 100 000 entrée et je dois effectuer plusieurs opérations sur cette table :
- la copié dans une temporaire (noté tmp)
- effacer la table actuel et la recharger par une nouvelle contenant des données plus récentes
- je dois ensuite comparé la tmp contenant l'ancienne table et la nouvelle pour recupérer les nouvelles entrée.
- ensuite je met à jour certain champs de l'actuel à partir de la tmp
- enfin je supprime la tmp
Cependant, mysql mais beaucoup de temps à executer ces fonctions (J'obtient : Fatal error: Maximum execution time of 60 seconds exceeded)
Le script fonctionne parfaitement quand la table comporte peu d'entrée (environ 15-20 entrées)
Structure des tables :
Actuel :
CREATE TABLE `actuel` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`id_dossier_parent` int( 11 ) NOT NULL ,
`dossier_racine` varchar( 255 ) NOT NULL ,
`fichier` varchar( 255 ) NOT NULL ,
`extension` varchar( 255 ) NOT NULL ,
`adresse` varchar( 255 ) NOT NULL ,
`taille` varchar( 255 ) NOT NULL ,
`niveau` int( 11 ) NOT NULL ,
`is_folder` tinyint( 1 ) NOT NULL ,
`nb_vote` int( 11 ) NOT NULL ,
`moy_vote` int( 11 ) NOT NULL ,
KEY `id` ( `id` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8;
tmp :
CREATE TABLE tmp
(
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`fichier` varchar( 255 ) NOT NULL ,
`nb_vote` int( 11 ) NOT NULL ,
`moy_vote` int( 11 ) NOT NULL ,
KEY `id` ( `id` )
)
Voici le code de mes différentes fonction :
Fonction de copie dans la tmp :
function copy_table_to_tmp($table)
{
// On crée une table tmp de stockage
mysql_query("CREATE TABLE tmp
(
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`fichier` varchar( 255 ) NOT NULL ,
`nb_vote` int( 11 ) NOT NULL ,
`moy_vote` int( 11 ) NOT NULL ,
KEY `id` ( `id` )
)");
mysql_query("INSERT INTO tmp SELECT id, fichier, nb_vote, moy_vote FROM $table");
return;
}
La fonction permettant d'effacer la table actuel et de la remplir:
// on recupere le nom de la table correspondant au type entré, on la vide et on la remplie
$retour_type = mysql_query("SELECT table_bdd FROM donnees WHERE type = '$type'");
while ($donnees_type = mysql_fetch_array($retour_type))
{
$table = $donnees_type['table_bdd'];
// on vide l ancienne table
mysql_query("TRUNCATE TABLE $table");
// on vérifie s il existe des sous-dossiers (par ex Series/Series1, Series/Series2, etc...)
$retour_ssdossiers = mysql_query("SELECT nom_dossier,sous_dossiers FROM donnees WHERE table_bdd = '$table'");
$donnees_ssdossiers = mysql_fetch_array($retour_ssdossiers);
$niveau_initial = 0;
$dossier_actuel = '';
$id_dossier_parent = 0;
if ($donnees_ssdossiers['sous_dossiers'] == 1)
// s il y a des sous-dossiers, cas particulier
{
//On ouvre le dossier principal (par ex Series)
$dir = opendir('../Telechargement/' . $donnees_ssdossiers['nom_dossier']);
//Pour chaque sous-dossier (par ex Series/Series1, Series/Series2, ...)
while (($element_dossier = readdir($dir)) != FALSE)
{
if ($element_dossier != '.' && $element_dossier != '..' && is_dir('../Telechargement/' . $donnees_ssdossiers['nom_dossier'] .'/'.$element_dossier))
// si l élément est bien un dossier (on sait jamais !!)
{
$dossier_racine = $donnees_ssdossiers['nom_dossier'] . '/' . $element_dossier;
liste_fichiers($table, $dossier_racine, $dossier_actuel, $niveau_initial, $id_dossier_parent);
}
}
}
else
// s il n y a aucun sous-dossier d alias, bah on appelle directement la fonction de listage de fichiers
{
$dossier_racine = $donnees_ssdossiers['nom_dossier'];
liste_fichiers($table, $dossier_racine, $dossier_actuel, $niveau_initial, $id_dossier_parent);
}
nb_fichiers_de_table($table);
}
Ensuite le code permettant de comparé la table actuel et la tmp
function compare_table($table, $type)
{
// On vide l ancienne table contenant les fichier supprimer et ceux rajouté
mysql_query("TRUNCATE TABLE new_data");
mysql_query("TRUNCATE TABLE old_data");
// Puis on compare les deux tables et on enregistre les changements.
// On recupere les fichiers rajouter
$retour = mysql_query("SELECT fichier FROM $table WHERE fichier NOT IN (SELECT fichier FROM tmp)");
while ($donnees = mysql_fetch_array($retour)) {
$new_fic = $donnees['fichier'];
mysql_query("INSERT INTO new_data VALUES('', '$new_fic', '$type')");
}
//Puis les fichiers supprimer
$retour = mysql_query("SELECT fichier FROM tmp WHERE fichier NOT IN (SELECT fichier FROM $table)");
while ($donnees = mysql_fetch_array($retour)) {
$old_fic = $donnees['fichier'];
mysql_query("INSERT INTO old_data VALUES('', '$old_fic', '$type')");
}
}
Le code de mise a jour de certaines donnée:
function update_vote($table)
{
$retour = mysql_query("SELECT id, fichier, nb_vote, moy_vote FROM $table");
while ($donnees = mysql_fetch_array($retour)) {
$fichier = $donnees['fichier'];
$retour_tmp = mysql_query("SELECT * FROM tmp");
while ($donnees_tmp = mysql_fetch_array($retour_tmp)) {
$fichier_tmp = $donnees_tmp['fichier'];
if ($fichier == $fichier_tmp)
{
$retour_vote = mysql_query("SELECT nb_vote, moy_vote FROM tmp WHERE fichier = '$fichier'");
$donnee_vote = mysql_fetch_array($retour_vote);
$nb_vote = $donnee_vote['nb_vote'];
$moy_vote = $donnee_vote['moy_vote'];
mysql_query("UPDATE $table SET moy_vote = $moy_vote WHERE fichier = '$fichier' LIMIT 1");
mysql_query("UPDATE $table SET nb_vote = $nb_vote WHERE fichier = '$fichier' LIMIT 1");
}
}
}
}
Enfin le code permettant d'effacer la tmp
function erase_tmp()
{
// On supprime la table tmp.
mysql_query("DROP TABLE `tmp`");
return;
}
Coté serveur :
- Ubuntu 10.4
- Pentium IV
- 1 Gb de ram
- freq 3 GHz
- Taille total de la BDD au format sql 20Mo environ (je si pas si sa aide)
Y a t-il un moyen de rendre le script plus rapide?
J'ai aussi vu qu'il était possible de configurer MySql pour rendre les accès à la BDD plus rapide via my.cnf.
Est-il aussi envisageable d'utiliser un moteur du style MEMORY?
Que me conseillez vous?
Merci d'avance.
Optimisation script SQL et acces BDD
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.