1

enter image description here

My business scenario is shown in the figure above. A user can create multiple products, a product can have multiple modules, and a module can have multiple parameters. The parameters include variable types, variable names, and variable values.

Before starting I thought the query speed of mptt was better than 2D relational table, but the result is completely opposite.

I now have two data table designs.

Option One：

CREATE TABLE `products`  (
  `product_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cfdversion` bigint(20) NULL DEFAULT NULL,
  `product_info` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `is_activated` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`product_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 226 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `person_param`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NULL DEFAULT NULL,
  `param_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `var_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `var_value` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `var_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `is_activated` tinyint(1) NULL DEFAULT 1,
  `compute_value` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `module_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `product_id`(`product_id`) USING BTREE,
  CONSTRAINT `person_param_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

I connect the id of the product information with the parameter table.

Option two: products table is same just table name is different. person_paramlike this:

CREATE TABLE `mptt_param`  (
  `node_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `node_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `lft` bigint(20) NOT NULL,
  `rgt` bigint(20) NOT NULL,
  `node_level` bigint(20) NOT NULL,
  PRIMARY KEY (`node_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

I added 200 products with 3 modules per product and 10 parameters per module.

option 1's sql statement:SELECT * from `products` a RIGHT JOIN `person_param` b ON a.product_id=b.product_id WHERE a.product_id=246; enter image description here

option 2's sql statement:SELECT * FROM mptt_param WHERE lft>=(SELECT lft FROM mptt_param WHERE node_name='246') AND rgt<=(SELECT rgt FROM mptt_param WHERE node_name='246') ; enter image description here

I don't know what the problem is, hope you can give me some advice

jason-lin
  • 35
  • 5
  • What is your (1 specific researched non-duplicate) question? PS [How to Optimize Queries in a Database - The Basics](https://stackoverflow.com/q/3191623/3404097) [What are your most common sql optimizations?](https://stackoverflow.com/q/1332778/3404097) [When and why are database joins expensive?](https://stackoverflow.com/q/173726/3404097) [Tips for asking a good SQL question](https://meta.stackoverflow.com/q/271055/3404097) [Asking query performance questions](https://dba.meta.stackexchange.com/q/3034/43932) [mre] – philipxy Jul 13 '22 at 04:59
  • A right-left tree is a cute trick, but terrible when it comes to implementation performance. Avoid it for any trees of more than trivial size. – Rick James Jul 14 '22 at 13:27

0 Answers0