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 = 223 
  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 = 223 
  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.10283

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 = 223 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
6351	Túi Canvas Trơn	Less Than Three	P	5.00	3
1516	Túi Vải Canvas Kala, Túi Tote Đa Năng Tiện Dụng, Màu Sắc Bắt Mắt, Phong Cách Ngọt Ngào, Tính Ứng Dụng Cao	Less Than Three	P	5.00	3
2653	Túi Vải Bias Nhật Hoạ, Họa Tiết Thiên Nhiên Cách Điệu, Chất Vải Canvas Bền Đẹp, Quà Tặng Cho Bản Thân Và Bạn Bè	Less Than Three	P	5.00	2
15235	Túi Canvas Square Đầy Phong Cách, Họa Tiết Repetition Nổi Bật, Túi Vuông Rộng Rãi, Phụ Kiện Xuống Phố	Less Than Three	P	5.00	1
40307	Túi Vải Canvas Đen Ngăn Ngoài, Phong Cách Tối Giản, Kiểu Dáng Vuông Vức, Quai Đeo Chắc Chắn, Tiện Lợi và Đa Năng, Phù Hợp Nhiều Phong Cách	Less Than Three	P	5.00	1
48571	Túi Vải Canvas Bias Nắng, Phong Cách Hàn Quốc, Túi Vải Thủ Công, Túi Tote, Túi Đi Chơi, Ứng Dụng Cao, Phong Cách Tối Giản, Quà Tặng Tinh Tế	Less Than Three	P		0