SELECT 
  jmj_product_prices.product_id, 
  COALESCE(
    jmj_master_products_storefront_min_price.price, 
    MIN(
      IF(
        jmj_product_prices.percentage_discount = 0, 
        jmj_product_prices.price, 
        jmj_product_prices.price - (
          jmj_product_prices.price * jmj_product_prices.percentage_discount
        )/ 100
      )
    )
  ) AS price 
FROM 
  jmj_product_prices 
  LEFT JOIN jmj_master_products_storefront_min_price ON jmj_master_products_storefront_min_price.product_id = jmj_product_prices.product_id 
  AND jmj_master_products_storefront_min_price.storefront_id = 1 
WHERE 
  jmj_product_prices.product_id IN (
    1676, 811, 812, 891, 892, 893, 929, 930, 
    982, 950, 955, 957, 959, 961, 964, 966, 
    834, 835, 836, 837, 838, 839, 840, 841, 
    842, 843, 845, 846, 847, 849, 850, 851, 
    852, 853, 854, 855, 859, 860, 861, 910, 
    916, 1531, 1534, 1439
  ) 
  AND jmj_product_prices.lower_limit = 1 
  AND jmj_product_prices.usergroup_id IN (0, 1) 
GROUP BY 
  jmj_product_prices.product_id

Query time 0.01104

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "193.55"
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "17.95"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "jmj_product_prices",
            "access_type": "range",
            "possible_keys": [
              "usergroup",
              "product_id",
              "lower_limit",
              "usergroup_id"
            ],
            "key": "product_id",
            "used_key_parts": [
              "product_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 90,
            "rows_produced_per_join": 17,
            "filtered": "19.94",
            "index_condition": "(`jmj_test`.`jmj_product_prices`.`product_id` in (1676,811,812,891,892,893,929,930,982,950,955,957,959,961,964,966,834,835,836,837,838,839,840,841,842,843,845,846,847,849,850,851,852,853,854,855,859,860,861,910,916,1531,1534,1439))",
            "cost_info": {
              "read_cost": "166.42",
              "eval_cost": "3.59",
              "prefix_cost": "170.01",
              "data_read_per_join": "430"
            },
            "used_columns": [
              "product_id",
              "price",
              "percentage_discount",
              "lower_limit",
              "usergroup_id"
            ],
            "attached_condition": "((`jmj_test`.`jmj_product_prices`.`lower_limit` = 1) and (`jmj_test`.`jmj_product_prices`.`usergroup_id` in (0,1)))"
          }
        },
        {
          "table": {
            "table_name": "jmj_master_products_storefront_min_price",
            "access_type": "ALL",
            "possible_keys": [
              "PRIMARY"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 17,
            "filtered": "100.00",
            "using_join_buffer": "Block Nested Loop",
            "cost_info": {
              "read_cost": "2.01",
              "eval_cost": "3.59",
              "prefix_cost": "175.61",
              "data_read_per_join": "287"
            },
            "used_columns": [
              "storefront_id",
              "product_id",
              "price"
            ],
            "attached_condition": "<if>(is_not_null_compl(jmj_master_products_storefront_min_price), ((`jmj_test`.`jmj_master_products_storefront_min_price`.`storefront_id` = 1) and (`jmj_test`.`jmj_master_products_storefront_min_price`.`product_id` = `jmj_test`.`jmj_product_prices`.`product_id`)), true)"
          }
        }
      ]
    }
  }
}

Result

product_id price
811 600.00000000
812 700.00000000
834 789.00000000
835 700.00000000
836 700.00000000
837 700.00000000
838 700.00000000
839 700.00000000
840 700.00000000
841 700.00000000
842 700.00000000
843 700.00000000
845 700.00000000
846 700.00000000
847 700.00000000
849 600.00000000
850 600.00000000
851 600.00000000
852 600.00000000
853 600.00000000
854 600.00000000
855 600.00000000
859 600.00000000
860 600.00000000
861 600.00000000
891 600.00000000
892 700.00000000
893 750.00000000
910 635.00000000
916 655.00000000
929 700.00000000
930 650.00000000
950 490.00000000
955 495.00000000
957 490.00000000
959 495.00000000
961 490.00000000
964 495.00000000
966 490.00000000
982 600.00000000
1439 100.00000000
1531 600.00000000
1534 550.00000000
1676 999.00000000