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 = 492 
  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 = 492 
  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.11452

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 = 492 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
1081	Nước Rửa Chén Từ Enzyme Cà Chua, Rửa Sạch Chất Bẩn Hiệu Qủa, Thành Phần Thiên Nhiên, An Toàn Sử Dụng, Lối Sống Xanh	Cô cà chua	P	4.72	18
1083	Nước Giặt Giũ Từ Enzyme Cà Chua, Làm Sạch Hiệu Quả, Mùi Hương Tự Nhiên Dịu Nhẹ, Thành Phần Lành Tính Và An Toàn, Tiết Kiệm Nước	Cô cà chua	P	5.00	9
1084	Nước Lau Nhà Từ Enzyme Cà Chua, Làm Sạch Nhanh Chóng, Loại Bỏ Mùi Hôi, Hương Thơm Tự Nhiên Từ Tinh Dầu Chanh, An Toàn Và Lành Tính	Cô cà chua	P	4.17	21
1082	Nước Rửa Tay Từ Enzyme, Thành Phần Tự Nhiên Từ Trái Cây Và Rau Củ, An Toàn Và Không Gây Hại, Tiện Lợi Và Dễ Sử Dụng	Cô cà chua	P	4.63	16
1085	Nước Cọ Toilet Từ Enzyme Cà Chua, Bảo Vệ Khỏi Nấm Mốc, Thành Phần Lành Tính, Hiệu Quả Trong Việc Tẩy Rửa, Ngăn Ngừa Mùi Khó Chịu	Cô cà chua	P	5.00	8
1086	Nước Sạch Tuốt Từ Enzyme Cà Chua, Làm Sạch Mạnh Mẽ, Tiết Kiệm Công Sức, Công Dụng Đa Năng Và Tiện Lợi, Vệ Sinh Cho Mọi Không Gian	Cô cà chua	P	4.59	17