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 = 1508 
  AND v.product_id IN (
    '67475', 
    '67476', 
    '67477', 
    '67478', 
    '67479', 
    '67480', 
    '67481', 
    '67482', 
    '67483', 
    '67484', 
    '67485', 
    '67486', 
    '67487', 
    '67488', 
    '67489', 
    '67490', 
    '67491', 
    '67492', 
    '67493', 
    '67494', 
    '67495', 
    '67496', 
    '67497', 
    '67498', 
    '67499', 
    '67500', 
    '67501', 
    '67502', 
    '67503', 
    '67504', 
    '67505', 
    '67506', 
    '67507', 
    '67508', 
    '67509', 
    '67510', 
    '67511', 
    '67512', 
    '67513', 
    '67514', 
    '67515', 
    '67516', 
    '67517', 
    '67518', 
    '67519', 
    '67520', 
    '67521', 
    '67522', 
    '67523', 
    '67524', 
    '67525', 
    '67526', 
    '67527', 
    '67528', 
    '67529', 
    '67530', 
    '67531', 
    '67532', 
    '67533', 
    '67534', 
    '67535', 
    '67536', 
    '67537', 
    '67538', 
    '67539', 
    '67540', 
    '67541', 
    '67542', 
    '67543', 
    '67544', 
    '67545', 
    '67546', 
    '67547', 
    '67548', 
    '67549', 
    '67550', 
    '67551', 
    '67552', 
    '67553', 
    '67554', 
    '67555', 
    '67556', 
    '67557', 
    '67558', 
    '67559', 
    '67560', 
    '67561', 
    '67562', 
    '67563', 
    '67564', 
    '67565', 
    '67566', 
    '67567', 
    '67568', 
    '67569', 
    '67570', 
    '67571', 
    '67572', 
    '67573', 
    '67574', 
    '67575', 
    '67576', 
    '67577', 
    '67578', 
    '67579', 
    '67580', 
    '67581', 
    '67582', 
    '67583', 
    '67584', 
    '67585', 
    '67586', 
    '67587', 
    '67588', 
    '67589', 
    '67590', 
    '67591', 
    '67592', 
    '67593', 
    '67594', 
    '67595', 
    '67596', 
    '67597', 
    '67598', 
    '67599', 
    '67600', 
    '67601', 
    '67602', 
    '67603', 
    '67604', 
    '67605', 
    '67606', 
    '67607', 
    '67608', 
    '67609', 
    '67610', 
    '67611', 
    '67612', 
    '67613', 
    '67614', 
    '67615', 
    '67616', 
    '67617', 
    '67618', 
    '67619', 
    '67620', 
    '67621', 
    '67622', 
    '67623', 
    '67624', 
    '67625', 
    '67626', 
    '67627', 
    '67628', 
    '67629', 
    '67630', 
    '67631', 
    '67632', 
    '67633', 
    '67634', 
    '67635', 
    '67636', 
    '67637', 
    '67638', 
    '67639', 
    '67640', 
    '67641', 
    '67642', 
    '67643', 
    '67644', 
    '67645', 
    '67646', 
    '67647', 
    '67648', 
    '67649', 
    '67650', 
    '67651', 
    '67652', 
    '67653', 
    '67654', 
    '67655', 
    '67656', 
    '67657', 
    '67658', 
    '67659', 
    '67660', 
    '67661', 
    '67662', 
    '67663', 
    '67664', 
    '67665', 
    '67666', 
    '67667', 
    '67668', 
    '67669', 
    '67670', 
    '67671', 
    '67672', 
    '67673', 
    '67674', 
    '67675', 
    '67676', 
    '67677', 
    '67678', 
    '67679', 
    '67680', 
    '67681', 
    '67682', 
    '67683', 
    '67684', 
    '67685', 
    '67686', 
    '67687', 
    '67688', 
    '67689', 
    '67690', 
    '67691', 
    '67692', 
    '67693', 
    '67694', 
    '67695', 
    '67696', 
    '67697', 
    '67698', 
    '67699', 
    '67700', 
    '67701', 
    '67702', 
    '67703', 
    '67704', 
    '67705', 
    '67706', 
    '67707', 
    '67708', 
    '67709', 
    '67710', 
    '67711', 
    '67712', 
    '67713', 
    '67714', 
    '67715', 
    '67716', 
    '67717', 
    '67718', 
    '67719', 
    '67720', 
    '67721', 
    '67722', 
    '67723', 
    '67724', 
    '67725', 
    '67726', 
    '67727', 
    '67728', 
    '67729', 
    '67730', 
    '67731', 
    '67732', 
    '67733', 
    '67734', 
    '67735', 
    '67736', 
    '67737', 
    '67738', 
    '67739', 
    '67740', 
    '67741', 
    '67742', 
    '67743', 
    '67744'
  )

Query time 0.00314

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` = 1508) and (`cscartdb`.`v`.`product_id` in ('67475','67476','67477','67478','67479','67480','67481','67482','67483','67484','67485','67486','67487','67488','67489','67490','67491','67492','67493','67494','67495','67496','67497','67498','67499','67500','67501','67502','67503','67504','67505','67506','67507','67508','67509','67510','67511','67512','67513','67514','67515','67516','67517','67518','67519','67520','67521','67522','67523','67524','67525','67526','67527','67528','67529','67530','67531','67532','67533','67534','67535','67536','67537','67538','67539','67540','67541','67542','67543','67544','67545','67546','67547','67548','67549','67550','67551','67552','67553','67554','67555','67556','67557','67558','67559','67560','67561','67562','67563','67564','67565','67566','67567','67568','67569','67570','67571','67572','67573','67574','67575','67576','67577','67578','67579','67580','67581','67582','67583','67584','67585','67586','67587','67588','67589','67590','67591','67592','67593','67594','67595','67596','67597','67598','67599','67600','67601','67602','67603','67604','67605','67606','67607','67608','67609','67610','67611','67612','67613','67614','67615','67616','67617','67618','67619','67620','67621','67622','67623','67624','67625','67626','67627','67628','67629','67630','67631','67632','67633','67634','67635','67636','67637','67638','67639','67640','67641','67642','67643','67644','67645','67646','67647','67648','67649','67650','67651','67652','67653','67654','67655','67656','67657','67658','67659','67660','67661','67662','67663','67664','67665','67666','67667','67668','67669','67670','67671','67672','67673','67674','67675','67676','67677','67678','67679','67680','67681','67682','67683','67684','67685','67686','67687','67688','67689','67690','67691','67692','67693','67694','67695','67696','67697','67698','67699','67700','67701','67702','67703','67704','67705','67706','67707','67708','67709','67710','67711','67712','67713','67714','67715','67716','67717','67718','67719','67720','67721','67722','67723','67724','67725','67726','67727','67728','67729','67730','67731','67732','67733','67734','67735','67736','67737','67738','67739','67740','67741','67742','67743','67744')))"
        }
      },
      {
        "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
6147 67475 Small 0 #000000
6147 67476 Small 0 #000000
6147 67477 Small 0 #000000
6148 67478 Medium 1 #000000
6148 67479 Medium 1 #000000
6148 67480 Medium 1 #000000
6149 67481 Large 2 #000000
6149 67482 Large 2 #000000
6149 67483 Large 2 #000000
6147 67484 Small 0 #000000
6147 67485 Small 0 #000000
6147 67486 Small 0 #000000
6148 67487 Medium 1 #000000
6148 67488 Medium 1 #000000
6148 67489 Medium 1 #000000
6149 67490 Large 2 #000000
6149 67491 Large 2 #000000
6149 67492 Large 2 #000000
6147 67493 Small 0 #000000
6147 67494 Small 0 #000000
6147 67495 Small 0 #000000
6148 67496 Medium 1 #000000
6148 67497 Medium 1 #000000
6148 67498 Medium 1 #000000
6149 67499 Large 2 #000000
6149 67500 Large 2 #000000
6149 67501 Large 2 #000000
6147 67502 Small 0 #000000
6147 67503 Small 0 #000000
6147 67504 Small 0 #000000
6148 67505 Medium 1 #000000
6148 67506 Medium 1 #000000
6148 67507 Medium 1 #000000
6149 67508 Large 2 #000000
6149 67509 Large 2 #000000
6149 67510 Large 2 #000000
6147 67511 Small 0 #000000
6147 67512 Small 0 #000000
6147 67513 Small 0 #000000
6148 67514 Medium 1 #000000
6148 67515 Medium 1 #000000
6148 67516 Medium 1 #000000
6149 67517 Large 2 #000000
6149 67518 Large 2 #000000
6149 67519 Large 2 #000000
6147 67520 Small 0 #000000
6147 67521 Small 0 #000000
6147 67522 Small 0 #000000
6148 67523 Medium 1 #000000
6148 67524 Medium 1 #000000
6148 67525 Medium 1 #000000
6149 67526 Large 2 #000000
6149 67527 Large 2 #000000
6149 67528 Large 2 #000000
6147 67529 Small 0 #000000
6147 67530 Small 0 #000000
6147 67531 Small 0 #000000
6148 67532 Medium 1 #000000
6148 67533 Medium 1 #000000
6148 67534 Medium 1 #000000
6149 67535 Large 2 #000000
6149 67536 Large 2 #000000
6149 67537 Large 2 #000000
6147 67538 Small 0 #000000
6147 67539 Small 0 #000000
6147 67540 Small 0 #000000
6148 67541 Medium 1 #000000
6148 67542 Medium 1 #000000
6148 67543 Medium 1 #000000
6149 67544 Large 2 #000000
6149 67545 Large 2 #000000
6149 67546 Large 2 #000000
6147 67547 Small 0 #000000
6147 67548 Small 0 #000000
6147 67549 Small 0 #000000
6148 67550 Medium 1 #000000
6148 67551 Medium 1 #000000
6148 67552 Medium 1 #000000
6149 67553 Large 2 #000000
6149 67554 Large 2 #000000
6149 67555 Large 2 #000000
6147 67556 Small 0 #000000
6147 67557 Small 0 #000000
6147 67558 Small 0 #000000
6148 67559 Medium 1 #000000
6148 67560 Medium 1 #000000
6148 67561 Medium 1 #000000
6149 67562 Large 2 #000000
6149 67563 Large 2 #000000
6149 67564 Large 2 #000000
6147 67565 Small 0 #000000
6147 67566 Small 0 #000000
6147 67567 Small 0 #000000
6148 67568 Medium 1 #000000
6148 67569 Medium 1 #000000
6148 67570 Medium 1 #000000
6149 67571 Large 2 #000000
6149 67572 Large 2 #000000
6149 67573 Large 2 #000000
6147 67574 Small 0 #000000
6147 67575 Small 0 #000000
6147 67576 Small 0 #000000
6148 67577 Medium 1 #000000
6148 67578 Medium 1 #000000
6148 67579 Medium 1 #000000
6149 67580 Large 2 #000000
6149 67581 Large 2 #000000
6149 67582 Large 2 #000000
6147 67583 Small 0 #000000
6147 67584 Small 0 #000000
6147 67585 Small 0 #000000
6148 67586 Medium 1 #000000
6148 67587 Medium 1 #000000
6148 67588 Medium 1 #000000
6149 67589 Large 2 #000000
6149 67590 Large 2 #000000
6149 67591 Large 2 #000000
6147 67592 Small 0 #000000
6147 67593 Small 0 #000000
6147 67594 Small 0 #000000
6148 67595 Medium 1 #000000
6148 67596 Medium 1 #000000
6148 67597 Medium 1 #000000
6149 67598 Large 2 #000000
6149 67599 Large 2 #000000
6149 67600 Large 2 #000000
6147 67601 Small 0 #000000
6147 67602 Small 0 #000000
6147 67603 Small 0 #000000
6148 67604 Medium 1 #000000
6148 67605 Medium 1 #000000
6148 67606 Medium 1 #000000
6149 67607 Large 2 #000000
6149 67608 Large 2 #000000
6149 67609 Large 2 #000000
6147 67610 Small 0 #000000
6147 67611 Small 0 #000000
6147 67612 Small 0 #000000
6148 67613 Medium 1 #000000
6148 67614 Medium 1 #000000
6148 67615 Medium 1 #000000
6149 67616 Large 2 #000000
6149 67617 Large 2 #000000
6149 67618 Large 2 #000000
6147 67619 Small 0 #000000
6147 67620 Small 0 #000000
6147 67621 Small 0 #000000
6148 67622 Medium 1 #000000
6148 67623 Medium 1 #000000
6148 67624 Medium 1 #000000
6149 67625 Large 2 #000000
6149 67626 Large 2 #000000
6149 67627 Large 2 #000000
6147 67628 Small 0 #000000
6147 67629 Small 0 #000000
6147 67630 Small 0 #000000
6148 67631 Medium 1 #000000
6148 67632 Medium 1 #000000
6148 67633 Medium 1 #000000
6149 67634 Large 2 #000000
6149 67635 Large 2 #000000
6149 67636 Large 2 #000000
6147 67637 Small 0 #000000
6147 67638 Small 0 #000000
6147 67639 Small 0 #000000
6148 67640 Medium 1 #000000
6148 67641 Medium 1 #000000
6148 67642 Medium 1 #000000
6149 67643 Large 2 #000000
6149 67644 Large 2 #000000
6149 67645 Large 2 #000000
6147 67646 Small 0 #000000
6147 67647 Small 0 #000000
6147 67648 Small 0 #000000
6148 67649 Medium 1 #000000
6148 67650 Medium 1 #000000
6148 67651 Medium 1 #000000
6149 67652 Large 2 #000000
6149 67653 Large 2 #000000
6149 67654 Large 2 #000000
6147 67655 Small 0 #000000
6147 67656 Small 0 #000000
6147 67657 Small 0 #000000
6148 67658 Medium 1 #000000
6148 67659 Medium 1 #000000
6148 67660 Medium 1 #000000
6149 67661 Large 2 #000000
6149 67662 Large 2 #000000
6149 67663 Large 2 #000000
6147 67664 Small 0 #000000
6147 67665 Small 0 #000000
6147 67666 Small 0 #000000
6148 67667 Medium 1 #000000
6148 67668 Medium 1 #000000
6148 67669 Medium 1 #000000
6149 67670 Large 2 #000000
6149 67671 Large 2 #000000
6149 67672 Large 2 #000000
6147 67673 Small 0 #000000
6147 67674 Small 0 #000000
6147 67675 Small 0 #000000
6148 67676 Medium 1 #000000
6148 67677 Medium 1 #000000
6148 67678 Medium 1 #000000
6149 67679 Large 2 #000000
6149 67680 Large 2 #000000
6149 67681 Large 2 #000000
6147 67682 Small 0 #000000
6147 67683 Small 0 #000000
6147 67684 Small 0 #000000
6148 67685 Medium 1 #000000
6148 67686 Medium 1 #000000
6148 67687 Medium 1 #000000
6149 67688 Large 2 #000000
6149 67689 Large 2 #000000
6149 67690 Large 2 #000000
6147 67691 Small 0 #000000
6147 67692 Small 0 #000000
6147 67693 Small 0 #000000
6148 67694 Medium 1 #000000
6148 67695 Medium 1 #000000
6148 67696 Medium 1 #000000
6149 67697 Large 2 #000000
6149 67698 Large 2 #000000
6149 67699 Large 2 #000000
6147 67700 Small 0 #000000
6147 67701 Small 0 #000000
6147 67702 Small 0 #000000
6148 67703 Medium 1 #000000
6148 67704 Medium 1 #000000
6148 67705 Medium 1 #000000
6149 67706 Large 2 #000000
6149 67707 Large 2 #000000
6149 67708 Large 2 #000000
6147 67709 Small 0 #000000
6147 67710 Small 0 #000000
6147 67711 Small 0 #000000
6148 67712 Medium 1 #000000
6148 67713 Medium 1 #000000
6148 67714 Medium 1 #000000
6149 67715 Large 2 #000000
6149 67716 Large 2 #000000
6149 67717 Large 2 #000000
6147 67718 Small 0 #000000
6147 67719 Small 0 #000000
6147 67720 Small 0 #000000
6148 67721 Medium 1 #000000
6148 67722 Medium 1 #000000
6148 67723 Medium 1 #000000
6149 67724 Large 2 #000000
6149 67725 Large 2 #000000
6149 67726 Large 2 #000000
6147 67727 Small 0 #000000
6147 67728 Small 0 #000000
6147 67729 Small 0 #000000
6148 67730 Medium 1 #000000
6148 67731 Medium 1 #000000
6148 67732 Medium 1 #000000
6149 67733 Large 2 #000000
6149 67734 Large 2 #000000
6149 67735 Large 2 #000000
6147 67736 Small 0 #000000
6147 67737 Small 0 #000000
6147 67738 Small 0 #000000
6148 67739 Medium 1 #000000
6148 67740 Medium 1 #000000
6148 67741 Medium 1 #000000
6149 67742 Large 2 #000000
6149 67743 Large 2 #000000
6149 67744 Large 2 #000000