Discussion :

[eprevot]

Bonjour,

Je débute en MySQL et j'aurai besoin de votre avis/expérience :
J'ai créé une base avec plusieurs tables, dont 1 qui contient 30 millions de lignes. Cette table fait dans les 3Go. Ca me parait énorme.
Quand j'exporte la base dans un fichier texte, j'ai un fichier de 700Mo et je pensais que ma base était 2x moins grosse que ça alors qu'elle est 4x plus grosse !

Est-ce que c'est un ordre de grandeur normal, 3Go pour 30M lignes ?
On peut paramétrer la base pour réduire la taille ?

[kabkab]

Bonjour,
Tout dépend de la structure de tes données. Si, par exemple, tu as une colonne que tu as déclaré en CHAR(150) alors que les enregistrements ne dépassent jamais 50 caractères pour cette colonne, tu as perdu 100 caractères multiplié par le nombre d'enregistrements ou alors tu définis une colonne avec INT et que tes enregistrements contiennent des chiffres de 0 à 255, tu aurais dû créer cette colonne avec TINYINT UNSIGNED parce que tu perds 3 octets multiplié par le nombre d'enregistrements.

Je te suggères de passer avec phpmyadmin ta table avec la fonction Suggérer des optimisations quant à la structure de la table Tu la trouveras en bas du tableau de structure de ta table. Il faut quand même faire attention, ça peut être assez lent avec de grandes tables.

Dans mon cas, j'ai une table de 120.000 enregistrements et elle occupe près de 700 Mo.

[eprevot]

Dans cette table j'ai 4 champs qui sont des int(8) unsigned.
Mais après avoir lu un peu de doc, il semble que la taille spécifiée dans les parenthèses ne joue pas. Il faut plutot passer en tinyint, smallint, mediumint...

Mais quand meme quelque chose de bizarre :
3G les 30M de lignes, ca fait 100 octets la ligne.
Or une ligne de 4 champs int (=4 octets) = 16 octets
Où est l'erreur ?

Sinon j'ai spécifié un champ comme 'primary', un couple de champs comme 'unique', et un champ comme 'index'.
est-ce que cela prend de la place ?

[eprevot]

J'ai adapté la structure de ma table pour avoir des smallint/mediumint au lieu des int. Ca ne change rien.
Par contre j'ai remarqué que les données prennent 900M et les indexes 2,2G.

J'ai 2 champs qui sont des clés étrangères donc je leur ai mis un index chacun, et en plus ces 2 champs sont un couple unique donc ils ont un index unique.
c'est mauvais de faire ça ?

[kabkab]

Bonjour,
est-ce que tu as pensé à Suggérer des optimisations quant à la structure de la table comme je t'en ai parlé?

[eprevot]

Non pas encore, j'ai bien peur que la requête n'aboutisse pas avec les 30M de lignes.
Deja pour supprimer un index, il a mis 4h...

[kabkab]

Binjour,
Est-ce tu pourrais poster ici la structure de ta table avec un peu d'explication sur les colonnes?

[haptomai]

Salut
Deux questions en attendant le schéma

- Quelle est la taille des indexes (si il y en a) ?
- Avez-vous pensez à optimiser la table ?

[eprevot]

Par exemple on représente une bibliothèque, on a 1 table des clients, 1 table des livres, 1 table des emprunts (innoDB) qui recense pour chaque client tous les livres qu'il a empruntés.
La table des emprunts a 4 colonnes :
- id : int (primary)
- client_id : smallint
- book_id : mediumint
- state : tinyint (0 ou 1)

La colonne client_id se réfère à la colone id de la table client, la colonne book_id se réfère à la colonne id de la table book. J'ai créé un index sur la colonne client_id et un index sur la colonne book_id. Lorsque un client ou un book est supprimé, les lignes correspondantes sont supprimées aussi par cascade. Ces 2 index pèsent en tout 1274M. (pour 940M de données)

Le state indique si le client a emprunté le livre ou s'il l'a rendu. S'il ré-emprunte le même livre, on modifie le state de la ligne qui existait déjà, il y a unicité sur le couple client_id/book_id. L'index unique sur ces 2 colonnes pèse 934M.

Je peux enlever l'index unique, mais lorsque j'enlève 1 des 2 index sur client_id ou book_id, cette requête n'aboutit jamais :
SELECT COUNT(*) FROM loan, client, book WHERE client.name = "toto"
AND book.author = "titi"
AND loan.client_id = client.id
AND loan.book_id = book.id

Remarque : la question originale était, est-ce que 3Go pour une table comme ça, ça vous semble normal? si c'est normal, eh bien soit, on achètera du dd. mais comme c'est la première fois que je fais du mysql, j'ai peur d'avoir fait n'importe quoi !

[vmolines]

Pour votre problème de performance, si ça se résume à la requête que vous présentez, c'est normal.

Vous sortez tous les emprunts en état 1 quelque soit le client et le book car vous ne faites aucune jointure entre loan et client ni loan et book. Vous balayez donc tous les emprunts même s'ils ne concernent pas le client et le book filtrés.

[eprevot]

En effet j'avais mal recopié ma requête en écrivant le post.
Voir le post précédent corrigé.
(cette requête me retourne un résultat instantané lorsque j'ai mes 2 indexs)

[CinePhil]

La table des emprunts a 4 colonnes :
- id : int (primary)
- client_id : smallint
- book_id : mediumint
- state : tinyint (0 ou 1)

J'ai créé un index sur la colonne client_id et un index sur la colonne book_id

il y a unicité sur le couple client_id/book_id

La contrainte d'unicité est chez MySQL un index UNIQUE. Comme cet index UNIQUE porte sur le couple (client_id, book_id), la colonne client_id est de facto indexée et l'index qui a été placé sur cette colonne individuellement peut être supprimé.

Code :

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

1
2
3
4
SELECT COUNT(*) FROM loan, client, book WHERE client.name = "toto"
AND book.author = "titi"
AND loan.client_id = client.id
AND loan.book_id = book.id

Avec la syntaxe des jointures en vigueur depuis 1992, ce serait mieux !

Code :

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

1
2
3
4
5
6
SELECT COUNT(*) AS nombre_emprunts
FROM loan l
INNER JOIN client c ON c.client_id = l.client_id
INNER JOIN book b ON b.id = l.book_id
WHERE c.name = 'toto'
	AND b.author = 'titi'

Et pour que ce genre de requête s'exécute rapidement, il faut des index sur client.name et book.author.

Pour la taille des index existants, un coup d'OPTIMIZE TABLE serait peut-être utile.

[eprevot]

cool j'en apprend des choses
J'ai modifié la requête, j'ai remis l'index unique sur le couple (client_id, book_id), et supprimé les 2 autres index.
Résultat : la requête aboutit aussi rapidement qu'avant, et la table fait maintenant 1835M, c'est toujours ça de gagné.

Par contre une chose :
Dans phpMyAdmin, quand je clique sur "gestion des relations" pour indiquer le delete en cascade sur client_id et book_id, je ne peux le faire que sur client_id. Il me dit que book_id n'a pas d'index.

[CinePhil]

Par contre une chose :
Dans phpMyAdmin, quand je clique sur "gestion des relations" pour indiquer le delete en cascade sur client_id et book_id, je ne peux le faire que sur client_id. Il me dit que exercise_id n'a pas d'index.

"exercise_id" ? Elle vient d'où cette colonne ?
Si elle n'est pas indexée, ajoute lui un index !

[eprevot]

oups coquille dans le post : pas exercise_id mais book_id

[CinePhil]

Bis repetita :

La contrainte d'unicité est chez MySQL un index UNIQUE. Comme cet index UNIQUE porte sur le couple (client_id, book_id), la colonne client_id est de facto indexée et l'index qui a été placé sur cette colonne individuellement peut être supprimé.

Je n'ai pas dit que book_id était de facto indexé, je n'ai parlé que de client_id !

Exemple de tri par l'index à double colonnes :
client_id, book_id
1, 1
1, 3
2, 5
2, 8
3, 2
3, 3

=> La première colonne est dans l'ordre mais la seconde ne l'ai que pour chaque valeur de la première !
Autrement dit, dans un index multi-colonnes, seule la première colonne est indexée individuellement. Les autres, si ce sont des clés étrangères ou si besoin, doivent être indexées individuellement, en plus de faire partie de l'index multi-colonnes.