SELECT 
  cscart_categories.category_id, 
  cscart_categories.parent_id, 
  cscart_categories.id_path, 
  cscart_category_descriptions.category, 
  cscart_categories.position, 
  cscart_categories.status, 
  cscart_categories.company_id, 
  cscart_categories.storefront_id, 
  cscart_categories.product_count, 
  cscart_seo_names.name as seo_name, 
  cscart_seo_names.path as seo_path, 
  cscart_categories.ab__lc_catalog_image_control, 
  cscart_companies.company 
FROM 
  cscart_categories 
  LEFT JOIN cscart_category_descriptions ON cscart_categories.category_id = cscart_category_descriptions.category_id 
  AND cscart_category_descriptions.lang_code = 'en' 
  LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_categories.category_id 
  AND cscart_seo_names.type = 'c' 
  AND cscart_seo_names.dispatch = '' 
  AND cscart_seo_names.lang_code = 'en' 
  LEFT JOIN cscart_companies ON cscart_companies.company_id = cscart_categories.company_id 
WHERE 
  1 = 1 
  AND (
    cscart_categories.usergroup_ids = '' 
    OR FIND_IN_SET(
      0, cscart_categories.usergroup_ids
    ) 
    OR FIND_IN_SET(
      1, cscart_categories.usergroup_ids
    )
  ) 
  AND cscart_categories.status IN ('A') 
  AND cscart_categories.category_id IN (577, 629, 631) 
  AND cscart_categories.category_id != 264 
  AND cscart_categories.parent_id != 264 
  AND cscart_categories.storefront_id IN (0, 1) 
  AND cscart_categories.category_id IN(
    166, 
    167, 
    170, 
    174, 
    190, 
    203, 
    204, 
    208, 
    219, 
    228, 
    229, 
    230, 
    231, 
    234, 
    235, 
    236, 
    245, 
    250, 
    458, 
    251, 
    305, 
    308, 
    320, 
    321, 
    322, 
    323, 
    324, 
    325, 
    326, 
    328, 
    329, 
    330, 
    331, 
    332, 
    333, 
    334, 
    336, 
    337, 
    339, 
    340, 
    341, 
    342, 
    543, 
    343, 
    344, 
    346, 
    347, 
    350, 
    351, 
    352, 
    353, 
    354, 
    355, 
    356, 
    357, 
    358, 
    359, 
    360, 
    361, 
    362, 
    363, 
    364, 
    366, 
    369, 
    371, 
    372, 
    378, 
    379, 
    380, 
    381, 
    382, 
    383, 
    384, 
    385, 
    386, 
    387, 
    393, 
    394, 
    395, 
    397, 
    398, 
    399, 
    400, 
    401, 
    402, 
    404, 
    405, 
    406, 
    407, 
    408, 
    409, 
    410, 
    411, 
    412, 
    413, 
    414, 
    415, 
    416, 
    417, 
    418, 
    419, 
    420, 
    421, 
    422, 
    423, 
    424, 
    425, 
    426, 
    428, 
    429, 
    430, 
    432, 
    434, 
    435, 
    436, 
    438, 
    439, 
    440, 
    441, 
    443, 
    444, 
    446, 
    448, 
    449, 
    450, 
    451, 
    452, 
    453, 
    454, 
    455, 
    457, 
    459, 
    460, 
    461, 
    462, 
    463, 
    464, 
    566, 
    465, 
    466, 
    467, 
    468, 
    469, 
    470, 
    471, 
    577, 
    473, 
    474, 
    475, 
    476, 
    477, 
    480, 
    481, 
    482, 
    483, 
    485, 
    486, 
    487, 
    488, 
    489, 
    490, 
    491, 
    492, 
    493, 
    494, 
    496, 
    497, 
    498, 
    499, 
    500, 
    502, 
    503, 
    504, 
    505, 
    506, 
    507, 
    513, 
    514, 
    515, 
    516, 
    517, 
    518, 
    519, 
    520, 
    521, 
    522, 
    523, 
    524, 
    525, 
    526, 
    527, 
    528, 
    530, 
    531, 
    532, 
    535, 
    536, 
    537, 
    538, 
    539, 
    540, 
    472, 
    542, 
    544, 
    545, 
    546, 
    547, 
    548, 
    552, 
    549, 
    550, 
    551, 
    553, 
    554, 
    555, 
    556, 
    557, 
    559, 
    560, 
    561, 
    562, 
    563, 
    564, 
    565, 
    567, 
    568, 
    569, 
    570, 
    571, 
    572, 
    573, 
    574, 
    575, 
    576, 
    578, 
    579, 
    580, 
    581, 
    582, 
    583, 
    584, 
    585, 
    586, 
    587, 
    534, 
    588, 
    590, 
    591, 
    592, 
    593, 
    594, 
    595, 
    596, 
    597, 
    598, 
    599, 
    600, 
    601, 
    602, 
    603, 
    612, 
    613, 
    614, 
    615, 
    616, 
    617, 
    618, 
    619, 
    620, 
    621, 
    622, 
    623, 
    624, 
    625, 
    626, 
    627, 
    558, 
    628, 
    629, 
    630, 
    631, 
    632, 
    633, 
    634, 
    635, 
    636, 
    637, 
    638, 
    639, 
    640, 
    641, 
    642, 
    643, 
    644, 
    645, 
    646, 
    647, 
    648, 
    649, 
    650, 
    651, 
    652, 
    653, 
    654, 
    655, 
    656, 
    657, 
    658, 
    659, 
    660, 
    661, 
    662, 
    663, 
    664, 
    666, 
    667, 
    668, 
    669
  ) 
ORDER BY 
  cscart_categories.is_trash asc, 
  cscart_categories.position asc, 
  cscart_category_descriptions.category asc

Query time 0.00203

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.93"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_categories",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "parent",
              "p_category_id"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id"
            ],
            "key_length": "3",
            "rows_examined_per_scan": 3,
            "rows_produced_per_join": 0,
            "filtered": "2.00",
            "index_condition": "((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`category_id` in (577,629,631)) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`category_id` <> 264) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`category_id` in (166,167,170,174,190,203,204,208,219,228,229,230,231,234,235,236,245,250,458,251,305,308,320,321,322,323,324,325,326,328,329,330,331,332,333,334,336,337,339,340,341,342,543,343,344,346,347,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,366,369,371,372,378,379,380,381,382,383,384,385,386,387,393,394,395,397,398,399,400,401,402,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,428,429,430,432,434,435,436,438,439,440,441,443,444,446,448,449,450,451,452,453,454,455,457,459,460,461,462,463,464,566,465,466,467,468,469,470,471,577,473,474,475,476,477,480,481,482,483,485,486,487,488,489,490,491,492,493,494,496,497,498,499,500,502,503,504,505,506,507,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,530,531,532,535,536,537,538,539,540,472,542,544,545,546,547,548,552,549,550,551,553,554,555,556,557,559,560,561,562,563,564,565,567,568,569,570,571,572,573,574,575,576,578,579,580,581,582,583,584,585,586,587,534,588,590,591,592,593,594,595,596,597,598,599,600,601,602,603,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,558,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,666,667,668,669)))",
            "cost_info": {
              "read_cost": "2.10",
              "eval_cost": "0.01",
              "prefix_cost": "2.11",
              "data_read_per_join": "192"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "company_id",
              "storefront_id",
              "usergroup_ids",
              "status",
              "product_count",
              "position",
              "is_trash",
              "ab__lc_catalog_image_control"
            ],
            "attached_condition": "(((`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`usergroup_ids`))) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`status` = 'A') and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`parent_id` <> 264) and (`softwarepirmam_hewadelivard_cscart_4`.`cscart_categories`.`storefront_id` in (0,1)))"
          }
        },
        {
          "table": {
            "table_name": "cscart_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.02",
              "eval_cost": "0.01",
              "prefix_cost": "2.13",
              "data_read_per_join": "204"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 66,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.99",
              "eval_cost": "0.40",
              "prefix_cost": "3.52",
              "data_read_per_join": "6K"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_companies",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "company_id"
            ],
            "key_length": "4",
            "ref": [
              "softwarepirmam_hewadelivard_cscart_4.cscart_categories.company_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 3,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.02",
              "eval_cost": "0.40",
              "prefix_cost": "3.93",
              "data_read_per_join": "24K"
            },
            "used_columns": [
              "company_id",
              "company"
            ]
          }
        }
      ]
    }
  }
}