Sandbox

SELECT 
  q_categories.category_id, 
  q_categories.parent_id, 
  q_categories.id_path, 
  q_category_descriptions.category, 
  q_categories.position, 
  q_categories.status, 
  q_seo_names.name as seo_name, 
  q_seo_names.path as seo_path, 
  q_categories.ab__lc_catalog_image_control 
FROM 
  q_categories 
  LEFT JOIN q_category_descriptions ON q_categories.category_id = q_category_descriptions.category_id 
  AND q_category_descriptions.lang_code = 'en' 
  LEFT JOIN q_seo_names ON q_seo_names.object_id = q_categories.category_id 
  AND q_seo_names.type = 'c' 
  AND q_seo_names.dispatch = '' 
  AND q_seo_names.lang_code = 'en' 
WHERE 
  1 = 1 
  AND (
    q_categories.usergroup_ids = '' 
    OR FIND_IN_SET(0, q_categories.usergroup_ids) 
    OR FIND_IN_SET(1, q_categories.usergroup_ids)
  ) 
  AND q_categories.status IN ('A') 
  AND q_categories.parent_id IN (190) 
  AND q_categories.id_path LIKE '190/%' 
  AND q_categories.category_id IN(
    2, 
    6, 
    12, 
    13, 
    14, 
    16, 
    17, 
    18, 
    23, 
    25, 
    27, 
    15, 
    37, 
    38, 
    40, 
    41, 
    42, 
    44, 
    46, 
    49, 
    50, 
    51, 
    58, 
    7, 
    61, 
    63, 
    64, 
    68, 
    71, 
    74, 
    76, 
    77, 
    79, 
    82, 
    84, 
    87, 
    88, 
    90, 
    604, 
    91, 
    92, 
    93, 
    94, 
    95, 
    96, 
    97, 
    144, 
    100, 
    106, 
    111, 
    113, 
    115, 
    124, 
    10, 
    134, 
    136, 
    138, 
    139, 
    141, 
    147, 
    148, 
    150, 
    151, 
    152, 
    153, 
    154, 
    159, 
    160, 
    163, 
    166, 
    167, 
    168, 
    169, 
    172, 
    176, 
    177, 
    179, 
    180, 
    183, 
    184, 
    185, 
    186, 
    187, 
    188, 
    189, 
    190, 
    191, 
    192, 
    193, 
    194, 
    195, 
    196, 
    197, 
    199, 
    200, 
    201, 
    202, 
    204, 
    206, 
    207, 
    217, 
    220, 
    221, 
    233, 
    234, 
    241, 
    243, 
    244, 
    248, 
    249, 
    250, 
    251, 
    252, 
    254, 
    255, 
    256, 
    257, 
    258, 
    261, 
    264, 
    266, 
    269, 
    271, 
    273, 
    274, 
    275, 
    276, 
    277, 
    278, 
    290, 
    293, 
    294, 
    296, 
    297, 
    298, 
    299, 
    301, 
    302, 
    303, 
    308, 
    313, 
    323, 
    329, 
    330, 
    352, 
    356, 
    360, 
    361, 
    362, 
    364, 
    367, 
    368, 
    369, 
    370, 
    371, 
    300, 
    375, 
    378, 
    379, 
    383, 
    384, 
    385, 
    386, 
    388, 
    389, 
    391, 
    401, 
    402, 
    410, 
    412, 
    413, 
    414, 
    417, 
    418, 
    419, 
    420, 
    422, 
    424, 
    425, 
    426, 
    428, 
    429, 
    430, 
    431, 
    432, 
    434, 
    435, 
    436, 
    437, 
    438, 
    439, 
    442, 
    443, 
    445, 
    446, 
    448, 
    450, 
    454, 
    455, 
    459, 
    460, 
    462, 
    465, 
    466, 
    469, 
    470, 
    471, 
    473, 
    475, 
    476, 
    477, 
    478, 
    481, 
    253, 
    489, 
    493, 
    495, 
    498, 
    499, 
    507, 
    509, 
    512, 
    513, 
    516, 
    518, 
    519, 
    520, 
    11, 
    523, 
    526, 
    527, 
    529, 
    532, 
    533, 
    535, 
    536, 
    537, 
    539, 
    540, 
    544, 
    545, 
    546, 
    547, 
    549, 
    550, 
    551, 
    555, 
    556, 
    557, 
    558, 
    559, 
    560, 
    561, 
    563, 
    564, 
    565, 
    566, 
    406, 
    567, 
    568, 
    570, 
    572, 
    576, 
    578, 
    577, 
    579, 
    581, 
    582, 
    584, 
    585, 
    135, 
    586, 
    587, 
    596, 
    597, 
    598, 
    601, 
    602, 
    603, 
    606
  ) 
ORDER BY 
  q_categories.is_trash asc, 
  q_categories.position asc, 
  q_category_descriptions.category asc

Query time 0.00162

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "24.12"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "nested_loop": [
        {
          "table": {
            "table_name": "q_categories",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "c_status",
              "parent",
              "id_path",
              "p_category_id"
            ],
            "key": "parent",
            "used_key_parts": [
              "parent_id"
            ],
            "key_length": "3",
            "ref": [
              "const"
            ],
            "rows_examined_per_scan": 20,
            "rows_produced_per_join": 0,
            "filtered": "0.25",
            "cost_info": {
              "read_cost": "20.00",
              "eval_cost": "0.01",
              "prefix_cost": "24.00",
              "data_read_per_join": "135"
            },
            "used_columns": [
              "category_id",
              "parent_id",
              "id_path",
              "usergroup_ids",
              "status",
              "position",
              "is_trash",
              "ab__lc_catalog_image_control"
            ],
            "attached_condition": "(((`portal`.`q_categories`.`usergroup_ids` = '') or find_in_set(0,`portal`.`q_categories`.`usergroup_ids`) or find_in_set(1,`portal`.`q_categories`.`usergroup_ids`)) and (`portal`.`q_categories`.`status` = 'A') and (`portal`.`q_categories`.`id_path` like '190/%') and (`portal`.`q_categories`.`category_id` in (2,6,12,13,14,16,17,18,23,25,27,15,37,38,40,41,42,44,46,49,50,51,58,7,61,63,64,68,71,74,76,77,79,82,84,87,88,90,604,91,92,93,94,95,96,97,144,100,106,111,113,115,124,10,134,136,138,139,141,147,148,150,151,152,153,154,159,160,163,166,167,168,169,172,176,177,179,180,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,199,200,201,202,204,206,207,217,220,221,233,234,241,243,244,248,249,250,251,252,254,255,256,257,258,261,264,266,269,271,273,274,275,276,277,278,290,293,294,296,297,298,299,301,302,303,308,313,323,329,330,352,356,360,361,362,364,367,368,369,370,371,300,375,378,379,383,384,385,386,388,389,391,401,402,410,412,413,414,417,418,419,420,422,424,425,426,428,429,430,431,432,434,435,436,437,438,439,442,443,445,446,448,450,454,455,459,460,462,465,466,469,470,471,473,475,476,477,478,481,253,489,493,495,498,499,507,509,512,513,516,518,519,520,11,523,526,527,529,532,533,535,536,537,539,540,544,545,546,547,549,550,551,555,556,557,558,559,560,561,563,564,565,566,406,567,568,570,572,576,578,577,579,581,582,584,585,135,586,587,596,597,598,601,602,603,606)))"
          }
        },
        {
          "table": {
            "table_name": "q_category_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "category_id",
              "lang_code"
            ],
            "key_length": "9",
            "ref": [
              "portal.q_categories.category_id",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.05",
              "eval_cost": "0.01",
              "prefix_cost": "24.06",
              "data_read_per_join": "155"
            },
            "used_columns": [
              "category_id",
              "lang_code",
              "category"
            ]
          }
        },
        {
          "table": {
            "table_name": "q_seo_names",
            "access_type": "ref",
            "possible_keys": [
              "PRIMARY",
              "dispatch"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "type",
              "dispatch",
              "lang_code"
            ],
            "key_length": "206",
            "ref": [
              "portal.q_categories.category_id",
              "const",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 0,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "0.05",
              "eval_cost": "0.01",
              "prefix_cost": "24.12",
              "data_read_per_join": "86"
            },
            "used_columns": [
              "name",
              "object_id",
              "type",
              "dispatch",
              "path",
              "lang_code"
            ]
          }
        }
      ]
    }
  }
}

Result

category_id	parent_id	id_path	category	position	status	seo_name	seo_path	ab__lc_catalog_image_control
551	190	190/551	Tablets	5	A	tablets	190	none
191	190	190/191	Cover Cases	10	A	cases	190	none
192	190	190/192	Screen Protectors	20	A	screen-protectors	190	none
193	190	190/193	Chargers & Power Adapters	30	A	chargers-and-power-adapters	190	none
194	190	190/194	Batteries & Power Banks	40	A	batteries-and-powerbanks	190	none
195	190	190/195	Mounts & Stands	50	A	mounts-and-stands	190	none
196	190	190/196	Selfie Sticks & Tripods	60	A	selfie-sticks-and-tripods	190	none
197	190	190/197	Mobile Game Controllers	70	A	mobile-game-controllers	190	none
199	190	190/199	Car Accessories	90	A	car-accessories	190	none
200	190	190/200	Corded Headsets	100	A	corded-headsets	190	none
201	190	190/201	Bluetooth Headsets	110	A	bluetooth-headsets	190	none
202	190	190/202	Cables & Connectors	120	A	selfie-sticks-and-tripods-en	190	none
204	190	190/204	Smart Watch Accessories	140	A	smart-watch-accessories	190	none
206	190	190/206	Smart Watches	150	A	smart-watches	190	none
386	190	190/386	Headset	160	A	headset	190	none
420	190	190/420	Keyboard	170	A	keyboard	190	none
424	190	190/424	Bluetooth Speakers	180	A	bluetooth-speakers	190	none
428	190	190/428	Mobile Holders & Stands	190	A	mobile-holders-and-stands	190	none
429	190	190/429	Mobile Tools & Accessories	200	A	mobile-tools-and-accessories	190	none
556	190	190/556	Tablet Accessories	210	A	tablet-accessories	190	none