Stockage du champ "body" des messages privés dans une table séparée pour gagner en rapidité

Question

J'ai un système de messagerie privée pour mes utilisateurs que j'ai créé en php avec un backend mysql. Le système supprime les anciens messages mais contient généralement plus de 500 000 messages. Actuellement, toutes les données sont incluses dans une table :

message_table
    message_id (int 11)
    message_from_id (int 11)
    message_to_id (int 11)
    message_timestamp (int 11)
    message_subject (varchar 50)
    message_text (text)

La majorité des messages étant très courts, j'envisage de modifier le système :

message_table
    message_id (int 11)
    message_from_id (int 11)
    message_to_id (int 11)
    message_timestamp (int 11)
    message_subject (varchar 50)
    message_short_body (varchar 50)
    message_text_id (int 11)

text_table
    text_id (int 11)
    text_body (text)

Ensuite, si un message court est saisi, il sera inscrit sous "message_short_body" et s'il est plus long, il sera ajouté à "text_table" et le "text_id" sera stocké sous "message_text_id". Lorsque les messages sont accessibles, j'obtiendrais quelque chose du genre :

SELECT * FROM message_table LEFT JOIN text_table ON text_table.text_id = message_table.message_text_id IF message_table.message_text_id != 0 WHERE message_table.message_to_id = $user_id

J'ai ajouté "IF message_table.message_text_id != 0" et je ne sais pas si quelque chose comme ça est possible.

En règle générale, est-il possible de savoir si cela réduirait la taille de la base de données ou accélérerait les requêtes ?

Answer 1

J'ai ajouté "IF message_table.message_text_id != 0" et je ne sais pas si quelque chose comme ça est possible.

A moins qu'il n'y ait effectivement une rangée avec text_id = 0 dans votre text_table il n'est pas nécessaire de le faire. Il suffit d'omettre le IF et utiliser la requête suivante :

SELECT IFNULL(text_table.text_body, message_table.message_short_body) AS body,
       …
FROM message_table
LEFT JOIN text_table ON text_table.text_id = message_table.message_text_id
WHERE message_table.message_to_id = $user_id

En termes de performances, il pourrait soit que le moteur puisse optimiser les choses plus efficacement si vous ajoutez votre condition aux conditions de jointure :

SELECT IFNULL(text_table.text_body, message_table.message_short_body) AS body,
       …
FROM message_table
LEFT JOIN text_table ON text_table.text_id = message_table.message_text_id
                    AND message_table.message_text_id != 0
WHERE message_table.message_to_id = $user_id

Vous pouvez également essayer une approche utilisant une sous-requête :

SELECT IF(message_text_id = 0, message_short_body, (
  SELECT text_table.message_short_body
  FROM text_table
  WHERE text_table.text_id = message_table.message_text_id)) AS body,
       …
FROM message_table
WHERE message_table.message_to_id = $user_id

Cela présente l'avantage de ne pas exécuter la recherche en text_table si aucun n'est requis, mais l'inconvénient d'effectuer une requête séparée pour chaque cas avec un long message. Je pense que les requêtes ci-dessus sont supérieures, mais je n'en suis pas sûr.

En règle générale, est-il possible de savoir si cela réduirait la taille de la base de données ou accélérerait les requêtes ?

Vous devrez faire un test de comparaison, car cela dépend du cas d'utilisation. Si la plupart de vos requêtes récupèrent des données dans les champs autres que le texte, la table plus petite rendra ces requêtes plus rapides, ce qui se traduira par un gain de performance. Si, par contre, vous voulez généralement le corps du message avec le reste du message, vous obtiendrez probablement de moins bonnes performances.

Vous devez également utiliser des repères pour distinguer les différentes alternatives décrites ci-dessus.

En termes de taille de la base de données, vous constaterez probablement une augmentation : les besoins de stockage pour les données textuelles sont à peu près les mêmes, mais les indices pour la table supplémentaire vous coûteront.

Je suppose que si c'était mon schéma, je laisserais tomber le message_text_id et avoir à la place la clé primaire du text_table correspond à celle de l message_table . C'est-à-dire que chaque clé se trouve soit uniquement dans la table des messages, soit dans les deux tables, et que les lignes ayant la même clé vont ensemble. Le fait que le message se trouve ou non dans l'autre table peut être codé en définissant les paramètres suivants message_table.message_short_body à NULL dans ces cas.

Answer 2

Essayez ça :

SELECT *, IFNULL(tt.text_body,  mt.message_short_body) textBody 
FROM message_table mt 
LEFT JOIN text_table tt ON tt.text_id = mt.message_text_id 
WHERE mt.message_to_id = $user_id;

Answer 3

J'ai ajouté "IF message_table.message_text_id != 0" et je ne sais pas si quelque chose comme ça est possible.

La requête que vous recherchez est la suivante :

SELECT
  IFNULL(t.text, m.short_text) AS text
  -- other columns may follow
FROM messages2 m
LEFT JOIN texts t on m.text_id = t.id
WHERE to_id = A_USER_ID

En règle générale, est-il possible de savoir si cela réduirait la taille de la base de données ou accélérerait les requêtes ?

Oui, c'est possible ! On peut au moins le tester. C'est ce que j'ai fait. J'ai créé un scénario de test avec un tableau de messages de 500.000 entrées. Chaque 10ème d'entre eux a un texte long. Les messages from_id et to_id sont choisis au hasard parmi 50 utilisateurs.

Partie 1 : Vitesse

La deuxième tentative, utilisant une table de textes séparée, donnera une BIGGGGGG accélération . Le temps moyen de requête pour la première tentative était de ~1.6 secondes. La seconde seulement ~0.28 secondes !!!!

Pour répondre à la question : Oui, c'est plus rapide ! :)

Partie 2 : Taille de la base de données

La taille de la base de données augmentera légèrement, comme on pouvait déjà s'y attendre. Les index supplémentaires des textes font que ma base de données augmente d'environ ~10%.

Conclusion : Stocker les textes importants dans un tableau séparé est une bon idée. Dans votre cas, elle améliorera les performances des requêtes jusqu'à 80 %, avec un coût disque légèrement supérieur de 10 %.