What is the best way to implement a category tree for an online store in SQL?

Question

MySQL

What is the best way to implement a list of categories with subcategories in a MySQL database for an online store?
Nesting level no more than 4:
Example: Household appliances -> Kitchen -> Coffee makers -> Capsule
The main thing you need is the speed of sampling from the database
Over time, subcategories can be added / removed , it should happen without hemorrhoids, what scheme would you recommend for use?
Adjacency List:

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL
  `name` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`parent_id`)
);

Nested Set:

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL
  `name` varchar(255) NOT NULL,
  `left` int(10) UNSIGNED NOT NULL,
  `right` int(10) UNSIGNED NOT NULL,
  `level` int(10) UNSIGNED NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`parent_id`)
);

Also tell me what structure is better to use for comments.

Answer 1

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED NOT NULL,
  `path_id` int(10) UNSIGNED NOT NULL,
  `related` JSON,
  `name` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX (`parent_id`)
);

Create a separate table category_path:
id | {path:[CATEGORY-ID-LEVEL-1, CATEGORY-ID-LEVEL-2, ...., CATEGORY-ID-PARENT, CATEGORY-ID-CURRENT]}
Store related/similar categories (their IDs) as needed :
{related:[CATEGORY-ID-X, CATEGORY-ID-Y, ..., CATEGORY-ID-N]}
This will help in one or two queries by ID:
1. Display breadcrumbs/breadcrumbs
2. Get category nesting depth in one request by ID
3. Display the hierarchy only up to the current category (without "diving" into unexpanded branches, which will significantly save server CPU).
4. Make a selection of similar categories for further search and display of similar offers.
5. Get the ID for all subcategories (and therefore their number) below any node to count the number of offers in the desired branch (including similar categories, if necessary).

Answer 2

See how the ruby ​​gem works https://github.com/stefankroes/ancestry
It stores all parents "11/23/145" as a string, as a result, LIKE queries are used instead of a bunch of recursive queries.

Answer 3

Of course nested sets (Nested Sets)
And the implementation is very simple and the speed is not comparable