Magento Search Re-index SQL - Product-EAV

[Genaker]

SELECT catalog_product_index_price.entity_id, catalog_product_index_price.customer_group_id, catalog_product_index_price.website_id, catalog_product_index_price.min_price FROM catalog_product_index_price WHERE (entity_id IN ()) - > []

SELECT e.entity_id, e.type_id, e.sku FROM catalog_product_entity AS e
INNER JOIN catalog_product_website AS website ON website.product_id = e.entity_id AND website.website_id = 4
INNER JOIN cataloginventory_stock_status AS stock_index ON stock_index.product_id = e.entity_id
INNER JOIN catalog_product_entity_int AS visibility_default ON visibility_default.row_id= e.row_id AND visibility_default.attribute_id = '102' AND visibility_default.store_id = 0
LEFT JOIN catalog_product_entity_int AS visibility_store ON visibility_store.row_id= e.row_id AND visibility_store.attribute_id = '102' AND visibility_store.store_id = 4
INNER JOIN catalog_product_entity_int AS status_default ON status_default.row_id= e.row_id AND status_default.attribute_id = '96' AND status_default.store_id = 0
LEFT JOIN catalog_product_entity_int AS status_store ON status_store.row_id= e.row_id AND status_store.attribute_id = '96' AND status_store.store_id = 4 WHERE ((IF(visibility_store.value_id > 0, visibility_store.value, visibility_default.value) IN (3, 2, 4)) AND (IF(status_store.value_id > 0, status_store.value, status_default.value) IN (1)) AND (e.entity_id > 115324) AND (e.entity_id < 117825)) AND (e.created_in <= '1630438980') AND (e.updated_in > '1630438980') ORDER BY e.entity_id ASC

SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_int AS t
LEFT JOIN catalog_product_entity_int AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_int AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (1319, 624 )) UNION ALL SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_varchar AS t
LEFT JOIN catalog_product_entity_varchar AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_varchar AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (1313, 1301, )) UNION ALL SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_text AS t
LEFT JOIN catalog_product_entity_text AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_text AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (72, 83, 73, 343, 332)) AND (t.row_id IN (2039, 11192, )) UNION ALL SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_decimal AS t
LEFT JOIN catalog_product_entity_decimal AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_decimal AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (75)) AND (t.row_id IN (......))

SELECT u.* FROM ( (SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_text AS t WHERE (row_id = '8183') AND (attribute_id IN (743, 746, 764, 749, 752, 1406, 755, 1409, 758, 1410, 44, 761, 1411, 1412, 1413, 47, 1414, 48, 54, 55, 56, 1424, 1425, 1428, 1440, 1441, 1443, 1444, 65, 721, 62, 1075, 711, 1079, 713, 1083, 1085, 715, 716)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_varchar AS t WHERE (row_id = '8183') AND (attribute_id IN (41, 1408, 43, 45, 46, 1415, 1416, 49, 58, 701, 1063, 1417, 1429, 1418, 1419, 1431, 1432, 1420, 1421, 1422, 57, 718, 1065, 1434, 1438, 1439, 1457, 1442, 705, 719, 1445, 720, 800, 1069, 133, 61, 66, 1071, 1139, 1073, 709, 710, 1077, 425, 1081, 660, 1087, 1089, 1091, 1375, 1378, 1381, 1387, 1390)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_int AS t WHERE (row_id = '8183') AND (attribute_id IN (42, 1407, 1446, 1447, 68, 69, 67, 703, 717, 1448, 1430, 1433, 1450, 1423, 1451, 1452, 50, 1435, 1426, 1436, 1427, 1437, 1456, 51, 1067, 1061, 1400, 426, 428, 1384)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_datetime AS t WHERE (row_id = '8183') AND (attribute_id IN (59, 60, 656, 657)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_decimal AS t WHERE (row_id = '8183') AND (attribute_id IN (70)) AND (store_id IN ('4', 0))) ) AS u ORDER BY store_id ASC

SELECT u.* FROM ( (SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_text AS t WHERE (row_id = '7279') AND (attribute_id IN (743, 746, 764, 749, 752, 1406, 755, 1409, 758, 1410, 44, 761, 1411, 1412, 1413, 47, 1414, 48, 54, 55, 56, 1424, 1425, 1428, 1440, 1441, 1443, 1444, 65, 721, 62, 1075, 711, 1079, 713, 1083, 1085, 715, 716)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_varchar AS t WHERE (row_id = '7279') AND (attribute_id IN (41, 1408, 43, 45, 46, 1415, 1416, 49, 58, 701, 1063, 1417, 1429, 1418, 1419, 1431, 1432, 1420, 1421, 1422, 57, 718, 1065, 1434, 1438, 1439, 1457, 1442, 705, 719, 1445, 720, 800, 1069, 133, 61, 66, 1071, 1139, 1073, 709, 710, 1077, 425, 1081, 660, 1087, 1089, 1091, 1375, 1378, 1381, 1387, 1390)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_int AS t WHERE (row_id = '7279') AND (attribute_id IN (42, 1407, 1446, 1447, 68, 69, 67, 703, 717, 1448, 1430, 1433, 1450, 1423, 1451, 1452, 50, 1435, 1426, 1436, 1427, 1437, 1456, 51, 1067, 1061, 1400, 426, 428, 1384)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_datetime AS t WHERE (row_id = '7279') AND (attribute_id IN (59, 60, 656, 657)) AND (store_id IN ('4', 0)))UNION ALL(SELECT t.value, t.attribute_id, t.store_id FROM catalog_category_entity_decimal AS t WHERE (row_id = '7279') AND (attribute_id IN (70)) AND (store_id IN ('4', 0))) ) AS u ORDER BY store_id ASC

SELECT cpe.row_id, cpe.entity_id FROM catalog_product_entity AS cpe WHERE ((cpe.entity_id IN (117190, 117195, 117201, 117204, 117207, ...))) AND (cpe.created_in <= '1630438980') AND (cpe.updated_in > '1630438980') - > []

SQL time [0.00033807754516602]

SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_int AS t
LEFT JOIN catalog_product_entity_int AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_int AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (1319, 624, 420, 463, ....)) UNION ALL SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_varchar AS t
LEFT JOIN catalog_product_entity_varchar AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_varchar AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (1313, 1301, 1307, 1295, 410, 84, 82, 727, 71, 1026, 413, 97, 1229)) AND (t.row_id IN (5600, 17314, 19355, ....)) UNION ALL SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_text AS t
LEFT JOIN catalog_product_entity_text AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_text AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (72, 83, 73, 343, 332)) AND (t.row_id IN (5600, 17314, 19355, 19381, 58763, 39463, 51842 ....)) UNION ALL SELECT DISTINCT t.row_id, t.attribute_id, IF(t_store.value_id > 0, t_store.value, t_default.value) AS value FROM catalog_product_entity_decimal AS t
LEFT JOIN catalog_product_entity_decimal AS t_store ON t.row_id=t_store.row_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN catalog_product_entity_decimal AS t_default ON t.row_id=t_default.row_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (75)) AND (t.row_id IN (5600, 17314, 19355, .....))