Re: MySQL: Big amount of tables

[Rick James]

> each user can se the list of 'liked' comments. But i don't know how to do.

CREATE TABLE Likes (
user_id MEDIUMINT UNSIGNED NOT NULL,
comment_id INT UNSIGNED NOT NULL,
PRIMARY KEY(user_id, comment_id),
INDEX(comment_id, user_id)
) ENGINE=InnoDB;

This is a "many-to-many relationship table".

The data for the table is very sparse since the typical user will "like" nothing, and the typical comment will not be "liked" by anyone. You _might_ get more than 10'000'000 rows in this table; that would be under a GB.

A _few_ users will like hundreds of comments. A _few_ comments will have hundred of likes. This other end of the spectrum is adequately handled by the two indexes I suggested.

See LEFT JOIN and GROUP_CONCAT().

> if i make a table named {'LK' + user uniqueID} for each user

No! Not a good design. And 100K tables would lead to other problems.