[SQL] Optimiser ? Mais

Le
Denis Beauregard
Bonjour,

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.

Optimiser ? Mais à quoi cela sert-il ?


Denis
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Otomatic
Le #26315306
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
Denis Beauregard
Le #26315322
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.
Le #26315356
On 2014-10-15, Denis Beauregard

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 #26315364
Le Wed, 15 Oct 2014 20:40:07 +0200, "Stephane T."

On 2014-10-15, Denis Beauregard

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
Publicité
Poster une réponse
Anonyme