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 = 401 
  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 = 401 
  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.13410

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 = 401 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
4604	Mẹ Và Bé Kangaroo Len Dáng Đứng, Size M, Chuột Túi Bằng Len, Thú Bông Mẹ Con Kangaroo, Thú Bông Len Mini	Bobi Craft	P	4.50	2
18160	Thỏ Len Đan Tay Màu Trắng Và Nâu, Thỏ Móc Len Nhồi Bông, Thú Bông Hình Thỏ, Đồ Chơi Dễ Thương, Quà Tặng Sinh Nhật Bé	Bobi Craft	P	5.00	2
4648	Loa Bluetooth Cừu Xanh Len Kể Chuyện, Cừu Xanh Kể Chuyện, Thú Nhồi Bông, Thú Bông Len Kèm Loa Bluetooth, Thú Nhồi Bông Bằng Len	Bobi Craft	P	5.00	2
4650	Búp Bê Len Lily, Búp Bê Len Handmade, Thú Len Nhồi Bông, Thú Bông Bằng Len Handmade, Búp Bê Móc Len, Búp Bê Len Giá Tốt	Bobi Craft	P	5.00	1
4599	Gấu Lizzie Len Dáng Đứng, Size M, Thú Bông Cho Bé Yêu, Gấu Bông Len Móc, Đồ Chơi Bằng Len, Gấu Bông Mini	Bobi Craft	P	5.00	2
6254	Cừu Barbara Len Tinh Nghịch, Thú Nhồi Bông, Thú Bông Móc Len, Đồ Chơi Len Handmade Đồ Chơi An Toàn, Quà Tặng Cho Bécừu barbara len tinh nghịch, thú nhồi bông, thú bông móc len, đồ chơi len handmade đồ chơi an toàn, quà tặng cho bé	Bobi Craft	P	5.00	2