OR is usually poorly optimized. Also, MySQL won't use more than one index in a query. Anyway, you have no index starting with n_group_id, so it has to check every row.
PRIMARY KEY (`c_id`),
KEY `IDX_START_RESOURCE` (`d_start`,`n_resource_id`),
KEY `IDX_RESOURCE_GROUP` (`n_resource_id`,`n_group_id`)
SELECT *
FROM t_event_calendar
WHERE n_resource_id = 4
OR n_group_id IN (1)
OR ( n_group_id IS NULL
AND n_resource_id IS NULL)
Turning OR into UNION might run faster:
PRIMARY KEY (`c_id`),
KEY `IDX_START_RESOURCE` (`d_start`,`n_resource_id`),
KEY `IDX_RESOURCE_GROUP` (`n_resource_id`,`n_group_id`),
KEY (n_group_id) -- new index
SELECT *
FROM t_event_calendar
WHERE n_resource_id = 4
UNION DISTINCT
SELECT *
FROM t_event_calendar
WHERE n_group_id IN (1)
UNION DISTINCT
SELECT *
FROM t_event_calendar
WHERE n_group_id IS NULL
AND n_resource_id IS NULL
A more obtuse way:
PRIMARY KEY (`c_id`),
KEY `IDX_START_RESOURCE` (`d_start`,`n_resource_id`),
KEY (`n_resource_id`,`n_group_id`, c_id) -- field added
SELECT b.*
FROM t_event_calendar a,
JOIN t_event_calendar b ON a.c_id = b.c_id
WHERE a.n_resource_id = 4
OR a.n_group_id IN (1)
OR ( a.n_group_id IS NULL
AND a.n_resource_id IS NULL)
This 'self join' will first (alias 'a') run through the last index ("Using index"), to find all the c_id's desired, then do the harder work of looking up the rows in the other table (alias 'b').
You have a bunch of VARCHAR fields, why do you have ROW_FORMAT=FIXED ? Those two fight each other.