CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-partitioning
EddyG
EddyG

Reputation: 2129

MySQL partition by foreign key

I have a chat database in MySQL.

'users' table

user_id(PK), user_name

'chat' table

chat_id(PK), user1_id(FK), user2_id(FK)

'messages' table

message_id(PK), chat_id(FK), user_from(FK), message_text, message_date

Since I expect to have millions of records in the messages table, I thought of partitioning it. Is it a good approach? And what type of partition has to be used here? I thought if I partition by chat_id so for each chat between two users I get a partition. In practice, as a result, each time all records from a partition will be retrieved, since all of them belong to the same chat. But, this means if I have 1 million chats, I have 1 million partitions. However, since chat_id is a foreign key, MySQL doesn't allow partitioning by chat_id.

Upvotes: 0

Views: 140

Answers (2)

Rick James
Rick James

Reputation: 142356

The main thing to understand about PARTITIONing is that it does not inherently provide any performance benefit.

There are a few exceptions. The only one that might apply is:

If you intend to delete "old" chats, say after 30 days, then the DELETE can be made more efficient by using DROP PARTITION.

More discussion: http://mysql.rjweb.org/doc.php/partitionmaint

Back to your specific questions:

"each chat between two users I get a partition" -- NO! Partitioning does not scale well. In general, database engines are designed to be efficient when doing DML things: select/insert/delete/update, but at the expense of DDL things: create/alter/drop.

"all of them belong to the same chat" -- This sounds like an attempt to help with "caching". Most of that can be achieved by carefully choosing indexes. The messages for a given chat can be "clustered" together with this technique in the messages table:

CREATE TABLE Messages (
    message_id BIGINT NOT NULL AUTO_INCREMENT,
    chat_id INT UNSIGNED NOT NULL,
    ...
    PRIMARY KEY(chat_id, message_id),  -- to cluster by chat
    INDEX(message_id)   -- to keep auto_increment happy
) ENGINE=InnoDB;

In almost all situations a 'purpose' for partitioning can be emulated by a suitable indexing scheme. (Corollary: One must redesign the indexes when switching to/from partitioning.)

"1 million partitions" -- 8K is the limit. And, there is at least one disk file per partition; operating systems do not like having a million files, especially in a single directory. Even 8K is stretching things.

Upvotes: 1

mkilincaslan
mkilincaslan

Reputation: 39

I think you can use 1 table for both chat table and messages table because this kind of management mechanism is better than your way.

You think there will be millions of messages so you thought I can split the tables so it will be easier but that kind of management is hard and not optimized i think!

I use a chat table with room_id, sender_id, receiver_id, message_text, message_date, seen fields. And it's easier to manage...

If you think i can have millions of messages then you can use 2 databases and load balancing then you can check messages and databases by make load balancing.

Upvotes: 0

Related Questions