SELECT 
  v.variant_id, 
  v.product_id, 
  vd.variant, 
  vf.position, 
  vf.color 
FROM 
  cscart_product_features_values v 
  LEFT JOIN cscart_product_feature_variant_descriptions vd ON v.variant_id = vd.variant_id 
  AND vd.lang_code = 'en' 
  LEFT JOIN cscart_product_feature_variants vf ON v.variant_id = vf.variant_id 
WHERE 
  1 
  AND v.feature_id = 1545 
  AND v.product_id IN (
    '71049', 
    '71050', 
    '71051', 
    '71052', 
    '71053', 
    '71054', 
    '71055', 
    '71056', 
    '71057', 
    '71058', 
    '71059', 
    '71060', 
    '71061', 
    '71062', 
    '71063', 
    '71064', 
    '71065', 
    '71066', 
    '71067', 
    '71068', 
    '71069', 
    '71070', 
    '71071', 
    '71072', 
    '71073', 
    '71074', 
    '71075', 
    '71076', 
    '71077', 
    '71078', 
    '71079', 
    '71080', 
    '71081', 
    '71082', 
    '71083', 
    '71084', 
    '71085', 
    '71086', 
    '71087', 
    '71088', 
    '71089', 
    '71090', 
    '71091', 
    '71092', 
    '71093', 
    '71094', 
    '71095', 
    '71096', 
    '71097', 
    '71098', 
    '71099', 
    '71100', 
    '71101', 
    '71102', 
    '71103', 
    '71104', 
    '71105', 
    '71106', 
    '71107', 
    '71108', 
    '71109', 
    '71110', 
    '71111', 
    '71112', 
    '71113', 
    '71114', 
    '71115', 
    '71116', 
    '71117', 
    '71118', 
    '71119', 
    '71120', 
    '71121', 
    '71122', 
    '71123', 
    '71124', 
    '71125', 
    '71126', 
    '71127', 
    '71128', 
    '71129', 
    '71130', 
    '71131', 
    '71132', 
    '71133', 
    '71134', 
    '71135', 
    '71136', 
    '71137', 
    '71138', 
    '71139', 
    '71140', 
    '71141', 
    '71142', 
    '71143', 
    '71144', 
    '71145', 
    '71146', 
    '71147', 
    '71148', 
    '71149', 
    '71150', 
    '71151', 
    '71152', 
    '71153', 
    '71154', 
    '71155', 
    '71156', 
    '71157', 
    '71158', 
    '71159', 
    '71160', 
    '71161', 
    '71162', 
    '71163', 
    '71164', 
    '71165', 
    '71166', 
    '71167', 
    '71168', 
    '71169', 
    '71170', 
    '71171', 
    '71172', 
    '71173', 
    '71174', 
    '71175', 
    '71176', 
    '71177', 
    '71178', 
    '71179', 
    '71180', 
    '71181', 
    '71182', 
    '71183', 
    '71184', 
    '71185', 
    '71186', 
    '71187', 
    '71188', 
    '71189', 
    '71190', 
    '71191', 
    '71192', 
    '71193', 
    '71194', 
    '71195', 
    '71196', 
    '71197', 
    '71198', 
    '71199', 
    '71200', 
    '71201', 
    '71202', 
    '71203', 
    '71204', 
    '71205', 
    '71206', 
    '71207', 
    '71208', 
    '71209', 
    '71210', 
    '71211', 
    '71212', 
    '71213', 
    '71214', 
    '71215', 
    '71216', 
    '71217', 
    '71218', 
    '71219', 
    '71220', 
    '71221', 
    '71222', 
    '71223', 
    '71224', 
    '71225', 
    '71226', 
    '71227', 
    '71228', 
    '71229', 
    '71230', 
    '71231', 
    '71232', 
    '71233', 
    '71234', 
    '71235', 
    '71236', 
    '71237', 
    '71238', 
    '71239', 
    '71240', 
    '71241', 
    '71242', 
    '71243', 
    '71244', 
    '71245', 
    '71246', 
    '71247', 
    '71248', 
    '71249', 
    '71250', 
    '71251', 
    '71252', 
    '71253', 
    '71254', 
    '71255', 
    '71256', 
    '71257', 
    '71258', 
    '71259', 
    '71260', 
    '71261', 
    '71262', 
    '71263', 
    '71264', 
    '71265', 
    '71266', 
    '71267', 
    '71268', 
    '71269', 
    '71270', 
    '71271', 
    '71272', 
    '71273', 
    '71274', 
    '71275', 
    '71276', 
    '71277', 
    '71278', 
    '71279', 
    '71280', 
    '71281', 
    '71282', 
    '71283', 
    '71284', 
    '71285', 
    '71286', 
    '71287', 
    '71288', 
    '71289', 
    '71290', 
    '71291', 
    '71292', 
    '71293', 
    '71294', 
    '71295', 
    '71296', 
    '71297', 
    '71298', 
    '71299', 
    '71300', 
    '71301', 
    '71302', 
    '71303', 
    '71304', 
    '71305', 
    '71306', 
    '71307', 
    '71308', 
    '71309', 
    '71310', 
    '71311', 
    '71312', 
    '71313', 
    '71314', 
    '71315', 
    '71316', 
    '71317', 
    '71318'
  )

Query time 0.00315

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "37.23"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "v",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "fl",
            "product_id",
            "fpl",
            "idx_product_feature_variant_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "feature_id",
            "product_id"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 270,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "3.26",
            "eval_cost": "0.54",
            "prefix_cost": "33.46",
            "data_read_per_join": "4K"
          },
          "used_columns": [
            "feature_id",
            "product_id",
            "variant_id"
          ],
          "attached_condition": "((`cscartdb`.`v`.`feature_id` = 1545) and (`cscartdb`.`v`.`product_id` in ('71049','71050','71051','71052','71053','71054','71055','71056','71057','71058','71059','71060','71061','71062','71063','71064','71065','71066','71067','71068','71069','71070','71071','71072','71073','71074','71075','71076','71077','71078','71079','71080','71081','71082','71083','71084','71085','71086','71087','71088','71089','71090','71091','71092','71093','71094','71095','71096','71097','71098','71099','71100','71101','71102','71103','71104','71105','71106','71107','71108','71109','71110','71111','71112','71113','71114','71115','71116','71117','71118','71119','71120','71121','71122','71123','71124','71125','71126','71127','71128','71129','71130','71131','71132','71133','71134','71135','71136','71137','71138','71139','71140','71141','71142','71143','71144','71145','71146','71147','71148','71149','71150','71151','71152','71153','71154','71155','71156','71157','71158','71159','71160','71161','71162','71163','71164','71165','71166','71167','71168','71169','71170','71171','71172','71173','71174','71175','71176','71177','71178','71179','71180','71181','71182','71183','71184','71185','71186','71187','71188','71189','71190','71191','71192','71193','71194','71195','71196','71197','71198','71199','71200','71201','71202','71203','71204','71205','71206','71207','71208','71209','71210','71211','71212','71213','71214','71215','71216','71217','71218','71219','71220','71221','71222','71223','71224','71225','71226','71227','71228','71229','71230','71231','71232','71233','71234','71235','71236','71237','71238','71239','71240','71241','71242','71243','71244','71245','71246','71247','71248','71249','71250','71251','71252','71253','71254','71255','71256','71257','71258','71259','71260','71261','71262','71263','71264','71265','71266','71267','71268','71269','71270','71271','71272','71273','71274','71275','71276','71277','71278','71279','71280','71281','71282','71283','71284','71285','71286','71287','71288','71289','71290','71291','71292','71293','71294','71295','71296','71297','71298','71299','71300','71301','71302','71303','71304','71305','71306','71307','71308','71309','71310','71311','71312','71313','71314','71315','71316','71317','71318')))"
        }
      },
      {
        "table": {
          "table_name": "vd",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id",
            "lang_code"
          ],
          "key_length": "9",
          "ref": [
            "cscartdb.v.variant_id",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.35",
            "eval_cost": "0.54",
            "prefix_cost": "35.35",
            "data_read_per_join": "16K"
          },
          "used_columns": [
            "variant_id",
            "variant",
            "lang_code"
          ]
        }
      },
      {
        "table": {
          "table_name": "vf",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "variant_id"
          ],
          "key_length": "3",
          "ref": [
            "cscartdb.v.variant_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.35",
            "eval_cost": "0.54",
            "prefix_cost": "37.23",
            "data_read_per_join": "6K"
          },
          "used_columns": [
            "variant_id",
            "color",
            "position"
          ]
        }
      }
    ]
  }
}

Result

variant_id product_id variant position color
6285 71049 Small 0 #000000
6285 71050 Small 0 #000000
6285 71051 Small 0 #000000
6286 71052 Medium 1 #000000
6286 71053 Medium 1 #000000
6286 71054 Medium 1 #000000
6287 71055 Large 2 #000000
6287 71056 Large 2 #000000
6287 71057 Large 2 #000000
6285 71058 Small 0 #000000
6285 71059 Small 0 #000000
6285 71060 Small 0 #000000
6286 71061 Medium 1 #000000
6286 71062 Medium 1 #000000
6286 71063 Medium 1 #000000
6287 71064 Large 2 #000000
6287 71065 Large 2 #000000
6287 71066 Large 2 #000000
6285 71067 Small 0 #000000
6285 71068 Small 0 #000000
6285 71069 Small 0 #000000
6286 71070 Medium 1 #000000
6286 71071 Medium 1 #000000
6286 71072 Medium 1 #000000
6287 71073 Large 2 #000000
6287 71074 Large 2 #000000
6287 71075 Large 2 #000000
6285 71076 Small 0 #000000
6285 71077 Small 0 #000000
6285 71078 Small 0 #000000
6286 71079 Medium 1 #000000
6286 71080 Medium 1 #000000
6286 71081 Medium 1 #000000
6287 71082 Large 2 #000000
6287 71083 Large 2 #000000
6287 71084 Large 2 #000000
6285 71085 Small 0 #000000
6285 71086 Small 0 #000000
6285 71087 Small 0 #000000
6286 71088 Medium 1 #000000
6286 71089 Medium 1 #000000
6286 71090 Medium 1 #000000
6287 71091 Large 2 #000000
6287 71092 Large 2 #000000
6287 71093 Large 2 #000000
6285 71094 Small 0 #000000
6285 71095 Small 0 #000000
6285 71096 Small 0 #000000
6286 71097 Medium 1 #000000
6286 71098 Medium 1 #000000
6286 71099 Medium 1 #000000
6287 71100 Large 2 #000000
6287 71101 Large 2 #000000
6287 71102 Large 2 #000000
6285 71103 Small 0 #000000
6285 71104 Small 0 #000000
6285 71105 Small 0 #000000
6286 71106 Medium 1 #000000
6286 71107 Medium 1 #000000
6286 71108 Medium 1 #000000
6287 71109 Large 2 #000000
6287 71110 Large 2 #000000
6287 71111 Large 2 #000000
6285 71112 Small 0 #000000
6285 71113 Small 0 #000000
6285 71114 Small 0 #000000
6286 71115 Medium 1 #000000
6286 71116 Medium 1 #000000
6286 71117 Medium 1 #000000
6287 71118 Large 2 #000000
6287 71119 Large 2 #000000
6287 71120 Large 2 #000000
6285 71121 Small 0 #000000
6285 71122 Small 0 #000000
6285 71123 Small 0 #000000
6286 71124 Medium 1 #000000
6286 71125 Medium 1 #000000
6286 71126 Medium 1 #000000
6287 71127 Large 2 #000000
6287 71128 Large 2 #000000
6287 71129 Large 2 #000000
6285 71130 Small 0 #000000
6285 71131 Small 0 #000000
6285 71132 Small 0 #000000
6286 71133 Medium 1 #000000
6286 71134 Medium 1 #000000
6286 71135 Medium 1 #000000
6287 71136 Large 2 #000000
6287 71137 Large 2 #000000
6287 71138 Large 2 #000000
6285 71139 Small 0 #000000
6285 71140 Small 0 #000000
6285 71141 Small 0 #000000
6286 71142 Medium 1 #000000
6286 71143 Medium 1 #000000
6286 71144 Medium 1 #000000
6287 71145 Large 2 #000000
6287 71146 Large 2 #000000
6287 71147 Large 2 #000000
6285 71148 Small 0 #000000
6285 71149 Small 0 #000000
6285 71150 Small 0 #000000
6286 71151 Medium 1 #000000
6286 71152 Medium 1 #000000
6286 71153 Medium 1 #000000
6287 71154 Large 2 #000000
6287 71155 Large 2 #000000
6287 71156 Large 2 #000000
6285 71157 Small 0 #000000
6285 71158 Small 0 #000000
6285 71159 Small 0 #000000
6286 71160 Medium 1 #000000
6286 71161 Medium 1 #000000
6286 71162 Medium 1 #000000
6287 71163 Large 2 #000000
6287 71164 Large 2 #000000
6287 71165 Large 2 #000000
6285 71166 Small 0 #000000
6285 71167 Small 0 #000000
6285 71168 Small 0 #000000
6286 71169 Medium 1 #000000
6286 71170 Medium 1 #000000
6286 71171 Medium 1 #000000
6287 71172 Large 2 #000000
6287 71173 Large 2 #000000
6287 71174 Large 2 #000000
6285 71175 Small 0 #000000
6285 71176 Small 0 #000000
6285 71177 Small 0 #000000
6286 71178 Medium 1 #000000
6286 71179 Medium 1 #000000
6286 71180 Medium 1 #000000
6287 71181 Large 2 #000000
6287 71182 Large 2 #000000
6287 71183 Large 2 #000000
6285 71184 Small 0 #000000
6285 71185 Small 0 #000000
6285 71186 Small 0 #000000
6286 71187 Medium 1 #000000
6286 71188 Medium 1 #000000
6286 71189 Medium 1 #000000
6287 71190 Large 2 #000000
6287 71191 Large 2 #000000
6287 71192 Large 2 #000000
6285 71193 Small 0 #000000
6285 71194 Small 0 #000000
6285 71195 Small 0 #000000
6286 71196 Medium 1 #000000
6286 71197 Medium 1 #000000
6286 71198 Medium 1 #000000
6287 71199 Large 2 #000000
6287 71200 Large 2 #000000
6287 71201 Large 2 #000000
6285 71202 Small 0 #000000
6285 71203 Small 0 #000000
6285 71204 Small 0 #000000
6286 71205 Medium 1 #000000
6286 71206 Medium 1 #000000
6286 71207 Medium 1 #000000
6287 71208 Large 2 #000000
6287 71209 Large 2 #000000
6287 71210 Large 2 #000000
6285 71211 Small 0 #000000
6285 71212 Small 0 #000000
6285 71213 Small 0 #000000
6286 71214 Medium 1 #000000
6286 71215 Medium 1 #000000
6286 71216 Medium 1 #000000
6287 71217 Large 2 #000000
6287 71218 Large 2 #000000
6287 71219 Large 2 #000000
6285 71220 Small 0 #000000
6285 71221 Small 0 #000000
6285 71222 Small 0 #000000
6286 71223 Medium 1 #000000
6286 71224 Medium 1 #000000
6286 71225 Medium 1 #000000
6287 71226 Large 2 #000000
6287 71227 Large 2 #000000
6287 71228 Large 2 #000000
6285 71229 Small 0 #000000
6285 71230 Small 0 #000000
6285 71231 Small 0 #000000
6286 71232 Medium 1 #000000
6286 71233 Medium 1 #000000
6286 71234 Medium 1 #000000
6287 71235 Large 2 #000000
6287 71236 Large 2 #000000
6287 71237 Large 2 #000000
6285 71238 Small 0 #000000
6285 71239 Small 0 #000000
6285 71240 Small 0 #000000
6286 71241 Medium 1 #000000
6286 71242 Medium 1 #000000
6286 71243 Medium 1 #000000
6287 71244 Large 2 #000000
6287 71245 Large 2 #000000
6287 71246 Large 2 #000000
6285 71247 Small 0 #000000
6285 71248 Small 0 #000000
6285 71249 Small 0 #000000
6286 71250 Medium 1 #000000
6286 71251 Medium 1 #000000
6286 71252 Medium 1 #000000
6287 71253 Large 2 #000000
6287 71254 Large 2 #000000
6287 71255 Large 2 #000000
6285 71256 Small 0 #000000
6285 71257 Small 0 #000000
6285 71258 Small 0 #000000
6286 71259 Medium 1 #000000
6286 71260 Medium 1 #000000
6286 71261 Medium 1 #000000
6287 71262 Large 2 #000000
6287 71263 Large 2 #000000
6287 71264 Large 2 #000000
6285 71265 Small 0 #000000
6285 71266 Small 0 #000000
6285 71267 Small 0 #000000
6286 71268 Medium 1 #000000
6286 71269 Medium 1 #000000
6286 71270 Medium 1 #000000
6287 71271 Large 2 #000000
6287 71272 Large 2 #000000
6287 71273 Large 2 #000000
6285 71274 Small 0 #000000
6285 71275 Small 0 #000000
6285 71276 Small 0 #000000
6286 71277 Medium 1 #000000
6286 71278 Medium 1 #000000
6286 71279 Medium 1 #000000
6287 71280 Large 2 #000000
6287 71281 Large 2 #000000
6287 71282 Large 2 #000000
6285 71283 Small 0 #000000
6285 71284 Small 0 #000000
6285 71285 Small 0 #000000
6286 71286 Medium 1 #000000
6286 71287 Medium 1 #000000
6286 71288 Medium 1 #000000
6287 71289 Large 2 #000000
6287 71290 Large 2 #000000
6287 71291 Large 2 #000000
6285 71292 Small 0 #000000
6285 71293 Small 0 #000000
6285 71294 Small 0 #000000
6286 71295 Medium 1 #000000
6286 71296 Medium 1 #000000
6286 71297 Medium 1 #000000
6287 71298 Large 2 #000000
6287 71299 Large 2 #000000
6287 71300 Large 2 #000000
6285 71301 Small 0 #000000
6285 71302 Small 0 #000000
6285 71303 Small 0 #000000
6286 71304 Medium 1 #000000
6286 71305 Medium 1 #000000
6286 71306 Medium 1 #000000
6287 71307 Large 2 #000000
6287 71308 Large 2 #000000
6287 71309 Large 2 #000000
6285 71310 Small 0 #000000
6285 71311 Small 0 #000000
6285 71312 Small 0 #000000
6286 71313 Medium 1 #000000
6286 71314 Medium 1 #000000
6286 71315 Medium 1 #000000
6287 71316 Large 2 #000000
6287 71317 Large 2 #000000
6287 71318 Large 2 #000000