· 7 years ago · Jan 22, 2019, 06:26 PM
1
2
3--
4-- Table structure for table `catalog_product_entity`
5--
6
7DROP TABLE IF EXISTS `catalog_product_entity`;
8/*!40101 SET @saved_cs_client = @@character_set_client */;
9/*!40101 SET character_set_client = utf8 */;
10CREATE TABLE `catalog_product_entity` (
11 `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
12 `entity_type_id` smallint(8) unsigned NOT NULL DEFAULT '0',
13 `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0',
14 `type_id` varchar(32) NOT NULL DEFAULT 'simple',
15 `sku` varchar(64) DEFAULT NULL,
16 `has_options` smallint(1) NOT NULL DEFAULT '0',
17 `required_options` tinyint(1) unsigned NOT NULL DEFAULT '0',
18 `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
19 `updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
20 PRIMARY KEY (`entity_id`),
21 KEY `FK_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE` (`entity_type_id`),
22 KEY `FK_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`),
23 KEY `sku` (`sku`),
24 CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` FOREIGN KEY (`attribute_set_id`) REFERENCES `eav_attribute_set` (`attribute_set_id`) ON DELETE CASCADE ON UPDATE CASCADE,
25 CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_ENTITY_TYPE` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
26) ENGINE=InnoDB AUTO_INCREMENT=9752 DEFAULT CHARSET=utf8 COMMENT='Product Entities';
27/*!40101 SET character_set_client = @saved_cs_client */;
28
29--
30-- Table structure for table `catalog_category_product`
31--
32
33DROP TABLE IF EXISTS `catalog_category_product`;
34/*!40101 SET @saved_cs_client = @@character_set_client */;
35/*!40101 SET character_set_client = utf8 */;
36CREATE TABLE `catalog_category_product` (
37 `category_id` int(10) unsigned NOT NULL DEFAULT '0',
38 `product_id` int(10) unsigned NOT NULL DEFAULT '0',
39 `position` int(10) NOT NULL DEFAULT '0',
40 UNIQUE KEY `UNQ_CATEGORY_PRODUCT` (`category_id`,`product_id`),
41 KEY `CATALOG_CATEGORY_PRODUCT_CATEGORY` (`category_id`),
42 KEY `CATALOG_CATEGORY_PRODUCT_PRODUCT` (`product_id`),
43 CONSTRAINT `CATALOG_CATEGORY_PRODUCT_CATEGORY` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
44 CONSTRAINT `CATALOG_CATEGORY_PRODUCT_PRODUCT` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
45) ENGINE=InnoDB DEFAULT CHARSET=utf8;
46/*!40101 SET character_set_client = @saved_cs_client */;
47
48--
49-- Table structure for table `catalog_category_entity_int`
50--
51
52DROP TABLE IF EXISTS `catalog_category_entity_int`;
53/*!40101 SET @saved_cs_client = @@character_set_client */;
54/*!40101 SET character_set_client = utf8 */;
55CREATE TABLE `catalog_category_entity_int` (
56 `value_id` int(11) NOT NULL AUTO_INCREMENT,
57 `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
58 `attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0',
59 `store_id` smallint(5) unsigned NOT NULL DEFAULT '0',
60 `entity_id` int(10) unsigned NOT NULL DEFAULT '0',
61 `value` int(11) DEFAULT NULL,
62 PRIMARY KEY (`value_id`),
63 UNIQUE KEY `IDX_BASE` (`entity_type_id`,`entity_id`,`attribute_id`,`store_id`),
64 KEY `FK_ATTRIBUTE_INT_ENTITY` (`entity_id`),
65 KEY `FK_CATALOG_CATEGORY_EMTITY_INT_ATTRIBUTE` (`attribute_id`),
66 KEY `FK_CATALOG_CATEGORY_EMTITY_INT_STORE` (`store_id`),
67 CONSTRAINT `FK_CATALOG_CATEGORY_EMTITY_INT_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
68 CONSTRAINT `FK_CATALOG_CATEGORY_EMTITY_INT_ENTITY` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
69 CONSTRAINT `FK_CATALOG_CATEGORY_EMTITY_INT_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE
70) ENGINE=InnoDB AUTO_INCREMENT=10258 DEFAULT CHARSET=utf8;
71/*!40101 SET character_set_client = @saved_cs_client */;
72
73--
74-- Table structure for table `core_store`
75--
76
77DROP TABLE IF EXISTS `core_store`;
78/*!40101 SET @saved_cs_client = @@character_set_client */;
79/*!40101 SET character_set_client = utf8 */;
80CREATE TABLE `core_store` (
81 `store_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
82 `code` varchar(32) NOT NULL DEFAULT '',
83 `website_id` smallint(5) unsigned DEFAULT '0',
84 `group_id` smallint(5) unsigned NOT NULL DEFAULT '0',
85 `name` varchar(255) NOT NULL,
86 `sort_order` smallint(5) unsigned NOT NULL DEFAULT '0',
87 `is_active` tinyint(1) unsigned NOT NULL DEFAULT '0',
88 PRIMARY KEY (`store_id`),
89 UNIQUE KEY `code` (`code`),
90 KEY `FK_STORE_WEBSITE` (`website_id`),
91 KEY `is_active` (`is_active`,`sort_order`),
92 KEY `FK_STORE_GROUP` (`group_id`),
93 CONSTRAINT `FK_STORE_GROUP_STORE` FOREIGN KEY (`group_id`) REFERENCES `core_store_group` (`group_id`) ON DELETE CASCADE ON UPDATE CASCADE,
94 CONSTRAINT `FK_STORE_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE
95) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8 COMMENT='Stores';
96/*!40101 SET character_set_client = @saved_cs_client */;
97
98--
99-- Table structure for table `catalog_product_website`
100--
101
102DROP TABLE IF EXISTS `catalog_product_website`;
103/*!40101 SET @saved_cs_client = @@character_set_client */;
104/*!40101 SET character_set_client = utf8 */;
105CREATE TABLE `catalog_product_website` (
106 `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
107 `website_id` smallint(5) unsigned NOT NULL,
108 PRIMARY KEY (`product_id`,`website_id`),
109 KEY `FK_CATALOG_PRODUCT_WEBSITE_WEBSITE` (`website_id`),
110 CONSTRAINT `FK_CATALOG_PRODUCT_WEBSITE_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE,
111 CONSTRAINT `FK_CATALOG_WEBSITE_PRODUCT_PRODUCT` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
112) ENGINE=InnoDB AUTO_INCREMENT=9752 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
113
114
115
116-- view definition
117
118CREATE ALGORITHM=UNDEFINED DEFINER=`mgr_b2bmavic`@`ncyl0110%` SQL SECURITY DEFINER VIEW `v_product_website_catalog` AS select `c`.`sku` AS `sku`,`ccp`.`product_id` AS `product_id`,`ccp`.`category_id` AS `category_id`,`cpw`.`website_id` AS `website_id` from ((((`catalog_product_entity` `c` join `catalog_category_product` `ccp` on((`ccp`.`product_id` = `c`.`entity_id`))) join `catalog_category_entity_int` `cce` on(((`cce`.`entity_id` = `ccp`.`category_id`) and (`cce`.`attribute_id` = 34) and (`cce`.`value` = 1) and (`cce`.`store_id` <> 0)))) join `core_store` `cs` on((`cs`.`store_id` = `cce`.`store_id`))) join `catalog_product_website` `cpw` on(((`cpw`.`product_id` = `ccp`.`product_id`) and (`cpw`.`website_id` <> 0)))) where (`cs`.`website_id` = `cpw`.`website_id`) group by `c`.`sku`,`ccp`.`product_id`,`ccp`.`category_id`,`cpw`.`website_id`;
119
120
121
122-- Unoptimized Query
123
124explain select category_id from v_product_website_catalog where website_id=4 AND product_id=3737;
125
126
127+----+-------------+------------+--------+-------------------------------------------------------------------------------------------------------+------------------------------------------+---------+---------------------------------------------+--------+----------------------------------------------+
128
129| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
130
131+----+-------------+------------+--------+-------------------------------------------------------------------------------------------------------+------------------------------------------+---------+---------------------------------------------+--------+----------------------------------------------+
132
133| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 125727 | Using where |
134
135| 2 | DERIVED | cce | ref | FK_ATTRIBUTE_INT_ENTITY,FK_CATALOG_CATEGORY_EMTITY_INT_ATTRIBUTE,FK_CATALOG_CATEGORY_EMTITY_INT_STORE | FK_CATALOG_CATEGORY_EMTITY_INT_ATTRIBUTE | 2 | | 2079 | Using where; Using temporary; Using filesort |
136
137| 2 | DERIVED | cs | eq_ref | PRIMARY,FK_STORE_WEBSITE | PRIMARY | 2 | b2bmavic.cce.store_id | 1 | Using where |
138
139| 2 | DERIVED | ccp | ref | UNQ_CATEGORY_PRODUCT,CATALOG_CATEGORY_PRODUCT_CATEGORY,CATALOG_CATEGORY_PRODUCT_PRODUCT | UNQ_CATEGORY_PRODUCT | 4 | b2bmavic.cce.entity_id | 67 | Using index |
140
141| 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | b2bmavic.ccp.product_id | 1 | |
142
143| 2 | DERIVED | cpw | eq_ref | PRIMARY,FK_CATALOG_PRODUCT_WEBSITE_WEBSITE | PRIMARY | 6 | b2bmavic.c.entity_id,b2bmavic.cs.website_id | 1 | Using where; Using index
144
145Query is 1s
146-- Rewriting the query pushing the condition in the SQL of the view is just fast
147
148select SQL_NO_CACHE `c`.`sku` AS `sku`,`ccp`.`product_id` AS `product_id`,`ccp`.`category_id` AS `category_id`,`cpw`.`website_id` AS `website_id` from ((((`catalog_product_entity` `c` join `catalog_category_product` `ccp` on((`ccp`.`product_id` = `c`.`entity_id`))) join `catalog_category_entity_int` `cce` on(((`cce`.`entity_id` = `ccp`.`category_id`) and (`cce`.`attribute_id` = 34) and (`cce`.`value` = 1) and (`cce`.`store_id` <> 0)))) join `core_store` `cs` on((`cs`.`store_id` = `cce`.`store_id`))) join `catalog_product_website` `cpw` on(((`cpw`.`product_id` = `ccp`.`product_id`) and (`cpw`.`website_id` <> 0)))) where (`cs`.`website_id` = `cpw`.`website_id`) AND cpw.website_id=4 AND ccp.product_id=3737 group by `c`.`sku`,`ccp`.`product_id`,`ccp`.`category_id`,`cpw`.`website_id`;
149
150+----------+------------+-------------+------------+
151| sku | product_id | category_id | website_id |
152+----------+------------+-------------+------------+
153| 30000034 | 3737 | 85 | 4 |
154+----------+------------+-------------+------------+
1551 row in set (0.00 sec)