Sandbox

SELECT 
  SQL_CALC_FOUND_ROWS products.product_id, 
  descr1.product as product, 
  companies.company as company_name, 
  products.product_type, 
  products.parent_product_id, 
  cscart_product_review_prepared_data.average_rating average_rating, 
  cscart_product_review_prepared_data.reviews_count product_reviews_count 
FROM 
  cscart_products as products 
  LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id 
  AND descr1.lang_code = 'vi' 
  LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id 
  AND prices.lower_limit = 1 
  LEFT JOIN cscart_companies AS companies ON companies.company_id = products.company_id 
  LEFT JOIN cscart_product_review_prepared_data ON cscart_product_review_prepared_data.product_id = products.product_id 
  AND cscart_product_review_prepared_data.storefront_id = 0 
  LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = products.product_id 
WHERE 
  1 
  AND companies.status IN ('A') 
  AND products.company_id = 372 
  AND (
    products.usergroup_ids = '' 
    OR FIND_IN_SET(0, products.usergroup_ids) 
    OR FIND_IN_SET(1, products.usergroup_ids)
  ) 
  AND products.status IN ('A') 
  AND prices.usergroup_id IN (0, 0, 1) 
  AND products.company_id = 372 
  AND products.parent_product_id = 0 
GROUP BY 
  products.product_id 
ORDER BY 
  popularity.total desc, 
  products.product_id ASC 
LIMIT 
  0, 6

Query time 0.17344

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "popularity.total desc, products.product_id",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "companies",
              "access_type": "const",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "4",
              "used_key_parts": ["company_id"],
              "ref": ["const"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "products",
              "access_type": "index_merge",
              "possible_keys": ["PRIMARY", "status", "idx_parent_product_id"],
              "key_length": "4,3",
              "index_merge": {
                "intersect": [
                  {
                    "range": {
                      "key": "idx_parent_product_id",
                      "used_key_parts": ["parent_product_id"]
                    }
                  },
                  {
                    "range": {
                      "key": "status",
                      "used_key_parts": ["status"]
                    }
                  }
                ]
              },
              "rows": 4670,
              "filtered": 100,
              "attached_condition": "products.company_id = 372 and products.parent_product_id = 0 and (products.usergroup_ids = '' or find_in_set(0,products.usergroup_ids) or find_in_set(1,products.usergroup_ids)) and products.`status` = 'A'"
            }
          },
          {
            "table": {
              "table_name": "descr1",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "product_id"],
              "key": "PRIMARY",
              "key_length": "11",
              "used_key_parts": ["product_id", "lang_code"],
              "ref": ["dev_db.products.product_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(descr1.lang_code = 'vi')"
            }
          },
          {
            "table": {
              "table_name": "cscart_product_review_prepared_data",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "7",
              "used_key_parts": ["product_id", "storefront_id"],
              "ref": ["dev_db.products.product_id", "const"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "popularity",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY", "total"],
              "key": "PRIMARY",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["dev_db.products.product_id"],
              "rows": 1,
              "filtered": 100
            }
          },
          {
            "table": {
              "table_name": "prices",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id",
                "lower_limit",
                "usergroup_id"
              ],
              "key": "product_id",
              "key_length": "3",
              "used_key_parts": ["product_id"],
              "ref": ["dev_db.products.product_id"],
              "rows": 1,
              "filtered": 99.9960556,
              "attached_condition": "prices.lower_limit = 1 and prices.usergroup_id in (0,0,1)"
            }
          }
        ]
      }
    }
  }
}

Result

product_id	product	company_name	product_type	average_rating	product_reviews_count
4098	Túi Móc Len Nâu Bạc Xỉu Cầm Tay	Bông Crochet	P	5.00	6
14965	Túi Móc Len Kof.ii, Thiết Kế Nhỏ Gọn XInh Xắn, Chất Liệu Túi Mềm Mại, Màu Sắc Tối Giản, Kỹ Thuật Móc Len Tinh Xảo, Phù Hợp Nhiều Phong Cách	Bông Crochet	P	5.00	3
5198	Túi Móc Len Xanh Matcha Latte Cầm Tay, Chất Liệu Len Dày Dặn, Độ Bền Cao, Thiết Kế Tối Giản, Có Thể Biến Hóa Nhiều Kiểu Đeo	Bông Crochet	P	5.00	7
4101	Thỏ Len Mochi, Thỏ Len Đan Tay, Thỏ Nhồi Bông Handmade, Thú Bông Móc Len Mini, Thú Bông Hình Con Thỏ	Bông Crochet	P	5.00	2
4102	Búp Bê Len Cute Deer, Búp Bê Bằng Len Móc, Búp Bê Len Handmade, Đồ Chơi Handmade Cho Bé, Thú Bông Len	Bông Crochet	P	4.80	5
4100	Thỏ Len Ellie, Thỏ Len Màu Hồng, Thú Bông Hình Con Thỏ, Thỏ Len Cao Cấp, Búp Bê Nhồi Bông Handmade, Quà Tặng Bé Gái	Bông Crochet	P	5.00	1