Discussion :

[CinePhil]

Bonjour,
J'aimerais savoir comment est physiquement structuré un index dans la machine.

Pour préciser ma question, je travaille actuellement sur de très grosses tables (plusieurs dizaines de millions de lignes) provenant de la fusion de fichiers texte. Il n'y a aucune clé primaire et encore moins en entier auto-incrémenté.
La clé candidate est sur deux colonnes, l'une en char(2) et l'autre en char(12).

J'ai construit un nouveau modèle de données normalisé et je suis en train de transférer les données des tables source vers la nouvelle base.
Si je fais des requêtes sans index, c'est hyper long.
L'ajout d'index sur les tables source prend également beaucoup de temps. J'ai un fichier d'index qui fait 1,5 Go.

Une requête, avec jointure entre la base source et la nouvelle base, pour préparer dans une table temporaire les lignes à ajouter à une table de la nouvelle base a pris plus de 14 heures !

Je me demandais donc si ce serait plus rapide en créant une clé primaire artificielle entière auto-incrémentée dans la table source, plutôt qu'en se basant sur une clé primaire double sur deux colonnes CHAR.
A noter : cette clé primaire ne me servira pas directement dans les requêtes d'importation.

Pour en revenir à ma question de départ :
Est-ce que les index supplémentaires qui m'aideraient à accélérer les requêtes d'importation seraient physiquement construits sur ma clé primaire auto-incrémentée ou bien les index sont-ils déjà physiquement construits à l'aide d'un numéro de ligne inaccessible à l'utilisateur ?

[qi130]

Une PK en auto-incrément ne permet que d'assurer l'unicité dans la table.

Dans ton cas, je ne vois pas bien en quoi elle améliorera les perfs (au contraire: + de datas à "trainer", index de PK inutilisé lors des jointures).

Par contre, avec la clé candidate, hormis l'investissement initial pour contruire l'index, je n'y vois que des bénéfices.

[CinePhil]

J'ai essayé la clé double mais MySQL a tourné un (long) moment avant de m'afficher une page blanche (phpMyAdmin). Je réaffiche la table : pas d'index !

Du coup j'ai passé la journée à indexer chaque colonne utile pour mon import et je n'ai pas fini. Ca continuera à tourner chez moi cette nuit (je prépare les données sur un portable).

Le fichier d'index fait déjà près de 3 Go pour un fichier de données de 5,4 Go (53 millions de lignes).

Je vais commencer à importer ce soir pour voir si mes index font gagner un peu de temps avant de poursuivre l'indexation sur les colonnes qui manquent mais qui interviennent dans une deuxième phase d'importation.

[gregory.broissard]

non une colonne en auto increment ne t'aidera pas. Pas plus que des index si ils ne sont pas filtrants.

La solution la meilleure serait de partitionner ta table :
http://krierjon.developpez.com/mysql/partitionnement/
Uniquement en 5.1, MySQL est assez en retard sur le sujet...

[CinePhil]

J'ai lu avec beaucoup d'intérêt hier cet article sur le partitionnement.
1er problème : il faudrait que nous passions notre serveur en version 5.1
2ème problème : les requêtes qui seront faites ne sont pas encore complètement connues et j'ai lu que le partitionnement est efficace surtout s'il est effectué en fonction des requêtes les plus fréquentes.

Mais je garde la solution en réserve pour la base finale.

J'ai écrit un autre appel à l'aide concernant la performance après des premiers tests effectués sur la plus grosse table.

[gregory.broissard]

le partitionnement est interessant si toutes les recherches effectuées utilisent la clé de partitionnement pour filtrer les données.

Si tu fais (par rapport à ton 2eme post) un count(distinct) . Meme sur une table partitionnée, ca n'ira pas beaucoup plus vite. Ca ira plus vite parce qu'il pourra paralléliser les opérations d'IOS mais il sera obligé de tout lire, tout trier, dédoublonner...