J'ai écrit un logiciel (PHP et mySQL) pour préparer une base de
données généalogiques. En gros, il insère dans une base SQL des
données de fichiers Excel et produit en bout de ligne des
pages web statiques pour consulter ces données. J'ai aussi
une version pour développement qui affiche directement telle
fiche.
Le système comprend plusieurs tables, de 200 000 à 700 000
enregistrements.
J'ai voulu optimiser une des opérations.
Une des tables contient 700 000 enregistrements et je faisais
un select qui prenait 15 secondes sur mon ordinateur portable
ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
J'ai d'abord remplacé le champ code de texte en varchar mais
cela n'a rien donné.
Ensuite, j'ai mis la donnée requise pour cette opération dans
une autre table (donc, ajout d'un champ) pour avoir plutôt
select table2 where no = 1234
Avant l'optimisation (sic), la génération d'une base html statique
se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
C'est dingue ! J'ai enlevé une opération qui est faite 136 fois et
qui dure 2,1 secondes selon phpmyadmin (donc 4,76 minutes) et cela
prend 1 minute de plus et non 4 minutes de moins. Comme si en ajoutant
un champ, j'aurais ajouté 10 minutes au temps de traitement alors que
je lis cette table avec des select qui reposent sur des champs avec
index.
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Otomatic
Denis Beauregard écrivait :
Avant l'optimisation (sic), la génération d'une base html statique se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
Lorsque l'on veut « mesurer » quelque chose, il est impératif : - 1 - Utiliser les mêmes moyens de mesure - 2 - Effectuer les mesures EXACTEMENT dans les mêmes conditions.
Vous ne pouvez absolument pas être certain que la condition n°2 ait été respectée dans les deux cas. Par exemple, anti-virus en fonctionnement, mises-à-jour de logiciels en tâches de fond, fichiers aux mêmes endroits sur le disque durs et fragmentés de la même manière, aucune autre tâche effectuée, etc. -- Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont forcément raison. Coluche
Avant l'optimisation (sic), la génération d'une base html statique
se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
Lorsque l'on veut « mesurer » quelque chose, il est impératif :
- 1 - Utiliser les mêmes moyens de mesure
- 2 - Effectuer les mesures EXACTEMENT dans les mêmes conditions.
Vous ne pouvez absolument pas être certain que la condition n°2 ait été
respectée dans les deux cas.
Par exemple, anti-virus en fonctionnement, mises-à-jour de logiciels en
tâches de fond, fichiers aux mêmes endroits sur le disque durs et
fragmentés de la même manière, aucune autre tâche effectuée, etc.
--
Ce n'est pas parce qu'ils sont nombreux à avoir tort
qu'ils ont forcément raison. Coluche
Avant l'optimisation (sic), la génération d'une base html statique se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
Lorsque l'on veut « mesurer » quelque chose, il est impératif : - 1 - Utiliser les mêmes moyens de mesure - 2 - Effectuer les mesures EXACTEMENT dans les mêmes conditions.
Vous ne pouvez absolument pas être certain que la condition n°2 ait été respectée dans les deux cas. Par exemple, anti-virus en fonctionnement, mises-à-jour de logiciels en tâches de fond, fichiers aux mêmes endroits sur le disque durs et fragmentés de la même manière, aucune autre tâche effectuée, etc. -- Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont forcément raison. Coluche
Denis Beauregard
Le Wed, 15 Oct 2014 18:58:29 +0200, Otomatic écrivait dans fr.comp.applications.sgbd:
Denis Beauregard écrivait :
Avant l'optimisation (sic), la génération d'une base html statique se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
Lorsque l'on veut « mesurer » quelque chose, il est impératif : - 1 - Utiliser les mêmes moyens de mesure - 2 - Effectuer les mesures EXACTEMENT dans les mêmes conditions.
Vous ne pouvez absolument pas être certain que la condition n°2 ait été respectée dans les deux cas. Par exemple, anti-virus en fonctionnement, mises-à-jour de logiciels en tâches de fond, fichiers aux mêmes endroits sur le disque durs et fragmentés de la même manière, aucune autre tâche effectuée, etc.
Les conditions sont presque les mêmes.
Dans le 1er cas (avant la modification), cela s'est fait de nuit.
Dans le 2e cas, j'ai très peu utilisé l'ordinateur (j'ai baissé de 4 minutes entre "faire autre chose" et "faire presque rien"), 2h14 puis 2h10, contre 2h09 avant.
Les bases SQL n'avaient pas été modifiées (sauf celle qui a été améliorée).
Mais je vais recommencer le test de nuit, en fermant toutes les autres tâches. Par contre, je ne suis pas à l'aise d'enlever le nouveau champ puisque cela déplacerait sans doute la table sur le disque. Alors, je vais tester avec et sans la correction dans le code (des SELECT différents).
Denis
Le Wed, 15 Oct 2014 18:58:29 +0200, Otomatic <otomatic@oto.invalid>
écrivait dans fr.comp.applications.sgbd:
Avant l'optimisation (sic), la génération d'une base html statique
se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
Lorsque l'on veut « mesurer » quelque chose, il est impératif :
- 1 - Utiliser les mêmes moyens de mesure
- 2 - Effectuer les mesures EXACTEMENT dans les mêmes conditions.
Vous ne pouvez absolument pas être certain que la condition n°2 ait été
respectée dans les deux cas.
Par exemple, anti-virus en fonctionnement, mises-à-jour de logiciels en
tâches de fond, fichiers aux mêmes endroits sur le disque durs et
fragmentés de la même manière, aucune autre tâche effectuée, etc.
Les conditions sont presque les mêmes.
Dans le 1er cas (avant la modification), cela s'est fait de nuit.
Dans le 2e cas, j'ai très peu utilisé l'ordinateur (j'ai baissé de 4
minutes entre "faire autre chose" et "faire presque rien"), 2h14 puis
2h10, contre 2h09 avant.
Les bases SQL n'avaient pas été modifiées (sauf celle qui a été
améliorée).
Mais je vais recommencer le test de nuit, en fermant toutes les autres
tâches. Par contre, je ne suis pas à l'aise d'enlever le nouveau champ
puisque cela déplacerait sans doute la table sur le disque. Alors, je
vais tester avec et sans la correction dans le code (des SELECT
différents).
Le Wed, 15 Oct 2014 18:58:29 +0200, Otomatic écrivait dans fr.comp.applications.sgbd:
Denis Beauregard écrivait :
Avant l'optimisation (sic), la génération d'une base html statique se faisait en 2h 9 min 51 sec. Après, cela a pris 2h 10 m 56 sec !!!
Lorsque l'on veut « mesurer » quelque chose, il est impératif : - 1 - Utiliser les mêmes moyens de mesure - 2 - Effectuer les mesures EXACTEMENT dans les mêmes conditions.
Vous ne pouvez absolument pas être certain que la condition n°2 ait été respectée dans les deux cas. Par exemple, anti-virus en fonctionnement, mises-à-jour de logiciels en tâches de fond, fichiers aux mêmes endroits sur le disque durs et fragmentés de la même manière, aucune autre tâche effectuée, etc.
Les conditions sont presque les mêmes.
Dans le 1er cas (avant la modification), cela s'est fait de nuit.
Dans le 2e cas, j'ai très peu utilisé l'ordinateur (j'ai baissé de 4 minutes entre "faire autre chose" et "faire presque rien"), 2h14 puis 2h10, contre 2h09 avant.
Les bases SQL n'avaient pas été modifiées (sauf celle qui a été améliorée).
Mais je vais recommencer le test de nuit, en fermant toutes les autres tâches. Par contre, je ne suis pas à l'aise d'enlever le nouveau champ puisque cela déplacerait sans doute la table sur le disque. Alors, je vais tester avec et sans la correction dans le code (des SELECT différents).
Denis
Stephane T.
On 2014-10-15, Denis Beauregard wrote:
Une des tables contient 700 000 enregistrements et je faisais un select qui prenait 15 secondes sur mon ordinateur portable ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
Si le type de requete est très courant et utilise toujours la première lettre d'un champ texte. Je rajouterais et indexerais un champ à la table qui contient cette information exact.
Genre
alter table test add column code_index char(1); update test set index_code = substring(code,1,1); create index index_code on test(index_code);
select * from test where index_code = 'a';
devrait être 100 fois plus rapide que
select * from test where index_code like 'a%';
Surtout sur une table à 700,000 entrées.
Toutes les commandes ci-dessus ont été testées sur un Postgres, il faut peut être adapter un peu sur MySQL.
Stéphane
-- Clothes make the man. Naked people have little or no influence on society.
On 2014-10-15, Denis Beauregard
<denis.b-at-francogene.com.invalid@nospam.com.invalid> wrote:
Une des tables contient 700 000 enregistrements et je faisais
un select qui prenait 15 secondes sur mon ordinateur portable
ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
Si le type de requete est très courant et utilise toujours la première
lettre d'un champ texte. Je rajouterais et indexerais un champ à la
table qui contient cette information exact.
Genre
alter table test add column code_index char(1);
update test set index_code = substring(code,1,1);
create index index_code on test(index_code);
select * from test where index_code = 'a';
devrait être 100 fois plus rapide que
select * from test where index_code like 'a%';
Surtout sur une table à 700,000 entrées.
Toutes les commandes ci-dessus ont été testées sur un Postgres, il faut
peut être adapter un peu sur MySQL.
Stéphane
--
Clothes make the man. Naked people have little or no influence on
society.
Une des tables contient 700 000 enregistrements et je faisais un select qui prenait 15 secondes sur mon ordinateur portable ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
Si le type de requete est très courant et utilise toujours la première lettre d'un champ texte. Je rajouterais et indexerais un champ à la table qui contient cette information exact.
Genre
alter table test add column code_index char(1); update test set index_code = substring(code,1,1); create index index_code on test(index_code);
select * from test where index_code = 'a';
devrait être 100 fois plus rapide que
select * from test where index_code like 'a%';
Surtout sur une table à 700,000 entrées.
Toutes les commandes ci-dessus ont été testées sur un Postgres, il faut peut être adapter un peu sur MySQL.
Stéphane
-- Clothes make the man. Naked people have little or no influence on society.
Denis Beauregard
Le Wed, 15 Oct 2014 20:40:07 +0200, "Stephane T." écrivait dans fr.comp.applications.sgbd:
On 2014-10-15, Denis Beauregard wrote:
Une des tables contient 700 000 enregistrements et je faisais un select qui prenait 15 secondes sur mon ordinateur portable ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
Si le type de requete est très courant et utilise toujours la première lettre d'un champ texte. Je rajouterais et indexerais un champ à la table qui contient cette information exact.
Je ne peux pas. Cette table est lue directement depuis les fichiers Excel et il y a déjà beaucoup de colonnes. De plus, j'aurais environ 20 fichiers à modifier. J'ai toutefois fait un autre changement.
Cette table est recopiée dans une 2e avec seulement les lignes où le code débute par A, donc ce 2e select n'a que no = 1234 et ce no est un INT.
Genre
alter table test add column code_index char(1); update test set index_code = substring(code,1,1); create index index_code on test(index_code);
select * from test where index_code = 'a';
devrait être 100 fois plus rapide que
select * from test where index_code like 'a%';
Surtout sur une table à 700,000 entrées.
Toutes les commandes ci-dessus ont été testées sur un Postgres, il faut peut être adapter un peu sur MySQL.
Merci mais il y a déjà eu ce genre d'optimisation. En gros, je lis dans une table A les fichiers Exel en format csv. Puis, je lis chaque ligne dans une table B, en conservant seulement certains champs. Le numéro de référence devient un champ INT et les dates converties en années par exemple. Cette table B a 2 champs indexés. Le premier a un index unique et une cardinalité de 149838, et le 2 aussi INT a un index BTREE de cardinalité 0.
Il y a aussi une table C de 130 000 entrées et qui n'a pas été modifiée. Celle-ci est aussi construite à partir de la table A selon d'autres critères.
Pour l'avoir essayé, j'ai vu qu'on peut indexer sur un VARCHAR ou un INT mais pas un TEXT. J'ai plusieurs index avec une cardinalité de 0, ce que je trouve étrange.
Ceci dit, je vois quelque chose d'étrange dans mes tables car je devrais avoir une table beaucoup plus grosse que 130 000 entrées.
Je dois ajouter que j'ai repris la même structure du logiciel que j'avais fait en C++ auparavant, et où je montais mes tables à la main (sans SGBD). J'ai donc beaucoup plus de tables que nécessaires. Mais j'ai l'impression que les champs ont tous un index quand je dois faire une recherche.
Denis
Le Wed, 15 Oct 2014 20:40:07 +0200, "Stephane T."
<stephane@unices.org> écrivait dans fr.comp.applications.sgbd:
On 2014-10-15, Denis Beauregard
<denis.b-at-francogene.com.invalid@nospam.com.invalid> wrote:
Une des tables contient 700 000 enregistrements et je faisais
un select qui prenait 15 secondes sur mon ordinateur portable
ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
Si le type de requete est très courant et utilise toujours la première
lettre d'un champ texte. Je rajouterais et indexerais un champ à la
table qui contient cette information exact.
Je ne peux pas. Cette table est lue directement depuis les fichiers
Excel et il y a déjà beaucoup de colonnes. De plus, j'aurais environ
20 fichiers à modifier. J'ai toutefois fait un autre changement.
Cette table est recopiée dans une 2e avec seulement les lignes où
le code débute par A, donc ce 2e select n'a que no = 1234 et ce
no est un INT.
Genre
alter table test add column code_index char(1);
update test set index_code = substring(code,1,1);
create index index_code on test(index_code);
select * from test where index_code = 'a';
devrait être 100 fois plus rapide que
select * from test where index_code like 'a%';
Surtout sur une table à 700,000 entrées.
Toutes les commandes ci-dessus ont été testées sur un Postgres, il faut
peut être adapter un peu sur MySQL.
Merci mais il y a déjà eu ce genre d'optimisation. En gros,
je lis dans une table A les fichiers Exel en format csv. Puis,
je lis chaque ligne dans une table B, en conservant seulement
certains champs. Le numéro de référence devient un champ INT et
les dates converties en années par exemple. Cette table B a
2 champs indexés. Le premier a un index unique et une cardinalité
de 149838, et le 2 aussi INT a un index BTREE de cardinalité 0.
Il y a aussi une table C de 130 000 entrées et qui n'a pas été
modifiée. Celle-ci est aussi construite à partir de la table A selon
d'autres critères.
Pour l'avoir essayé, j'ai vu qu'on peut indexer sur un VARCHAR
ou un INT mais pas un TEXT. J'ai plusieurs index avec une
cardinalité de 0, ce que je trouve étrange.
Ceci dit, je vois quelque chose d'étrange dans mes tables car je
devrais avoir une table beaucoup plus grosse que 130 000 entrées.
Je dois ajouter que j'ai repris la même structure du logiciel que
j'avais fait en C++ auparavant, et où je montais mes tables à la
main (sans SGBD). J'ai donc beaucoup plus de tables que nécessaires.
Mais j'ai l'impression que les champs ont tous un index quand je
dois faire une recherche.
Le Wed, 15 Oct 2014 20:40:07 +0200, "Stephane T." écrivait dans fr.comp.applications.sgbd:
On 2014-10-15, Denis Beauregard wrote:
Une des tables contient 700 000 enregistrements et je faisais un select qui prenait 15 secondes sur mon ordinateur portable ou 2 sur mon ordinateur de tables. Le select était du genre
select table where no = 1234 and code like "a%"
Si le type de requete est très courant et utilise toujours la première lettre d'un champ texte. Je rajouterais et indexerais un champ à la table qui contient cette information exact.
Je ne peux pas. Cette table est lue directement depuis les fichiers Excel et il y a déjà beaucoup de colonnes. De plus, j'aurais environ 20 fichiers à modifier. J'ai toutefois fait un autre changement.
Cette table est recopiée dans une 2e avec seulement les lignes où le code débute par A, donc ce 2e select n'a que no = 1234 et ce no est un INT.
Genre
alter table test add column code_index char(1); update test set index_code = substring(code,1,1); create index index_code on test(index_code);
select * from test where index_code = 'a';
devrait être 100 fois plus rapide que
select * from test where index_code like 'a%';
Surtout sur une table à 700,000 entrées.
Toutes les commandes ci-dessus ont été testées sur un Postgres, il faut peut être adapter un peu sur MySQL.
Merci mais il y a déjà eu ce genre d'optimisation. En gros, je lis dans une table A les fichiers Exel en format csv. Puis, je lis chaque ligne dans une table B, en conservant seulement certains champs. Le numéro de référence devient un champ INT et les dates converties en années par exemple. Cette table B a 2 champs indexés. Le premier a un index unique et une cardinalité de 149838, et le 2 aussi INT a un index BTREE de cardinalité 0.
Il y a aussi une table C de 130 000 entrées et qui n'a pas été modifiée. Celle-ci est aussi construite à partir de la table A selon d'autres critères.
Pour l'avoir essayé, j'ai vu qu'on peut indexer sur un VARCHAR ou un INT mais pas un TEXT. J'ai plusieurs index avec une cardinalité de 0, ce que je trouve étrange.
Ceci dit, je vois quelque chose d'étrange dans mes tables car je devrais avoir une table beaucoup plus grosse que 130 000 entrées.
Je dois ajouter que j'ai repris la même structure du logiciel que j'avais fait en C++ auparavant, et où je montais mes tables à la main (sans SGBD). J'ai donc beaucoup plus de tables que nécessaires. Mais j'ai l'impression que les champs ont tous un index quand je dois faire une recherche.
