Sandbox

SELECT 
  gp.*, 
  p.company_id 
FROM 
  cscart_product_variation_group_products AS gp 
  LEFT JOIN cscart_products AS p ON gp.product_id = p.product_id 
WHERE 
  gp.group_id IN (2003)

Query time 0.00087

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "122.40"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "gp",
          "access_type": "ref",
          "possible_keys": [
            "idx_group_id"
          ],
          "key": "idx_group_id",
          "used_key_parts": [
            "group_id"
          ],
          "key_length": "3",
          "ref": [
            "const"
          ],
          "rows_examined_per_scan": 51,
          "rows_produced_per_join": 51,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "51.00",
            "eval_cost": "10.20",
            "prefix_cost": "61.20",
            "data_read_per_join": "816"
          },
          "used_columns": [
            "product_id",
            "parent_product_id",
            "group_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "p",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "product_id"
          ],
          "key_length": "3",
          "ref": [
            "webmarco.gp.product_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 51,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "51.00",
            "eval_cost": "10.20",
            "prefix_cost": "122.40",
            "data_read_per_join": "398K"
          },
          "used_columns": [
            "product_id",
            "company_id"
          ]
        }
      }
    ]
  }
}

Result

product_id	parent_product_id	group_id	company_id
82038	0	2003	174
148882	82038	2003	174
148883	82038	2003	174
148884	82038	2003	174
148885	82038	2003	174
148886	82038	2003	174
148887	82038	2003	174
148888	82038	2003	174
148889	82038	2003	174
148890	82038	2003	174
148891	82038	2003	174
148892	82038	2003	174
148893	82038	2003	174
148894	82038	2003	174
148895	82038	2003	174
148896	82038	2003	174
148897	82038	2003	174
148898	82038	2003	174
148899	82038	2003	174
148900	82038	2003	174
148901	82038	2003	174
148902	82038	2003	174
148903	82038	2003	174
148904	82038	2003	174
148905	82038	2003	174
148906	82038	2003	174
148907	82038	2003	174
148908	82038	2003	174
148909	82038	2003	174
148910	82038	2003	174
148911	82038	2003	174
148912	82038	2003	174
148913	82038	2003	174
148914	82038	2003	174
148915	82038	2003	174
148916	82038	2003	174
148917	82038	2003	174
148918	82038	2003	174
148919	82038	2003	174
148920	82038	2003	174
148921	82038	2003	174
148922	82038	2003	174
148923	82038	2003	174
148924	82038	2003	174
148925	82038	2003	174
148926	82038	2003	174
148927	82038	2003	174
148928	82038	2003	174
148929	82038	2003	174
148930	82038	2003	174
148931	82038	2003	174