The optimization process and recommendations:

1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
2. Avoid Selecting Unnecessary Columns (query line: 11): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
3. Avoid Selecting Unnecessary Columns (query line: 20): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
4. Avoid Selecting Unnecessary Columns (query line: 29): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
5. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `a` ADD INDEX `a_idx_a_b` (`a`,`b`);
ALTER TABLE `a` ADD INDEX `a_idx_a_c` (`a`,`c`);

The optimized query:

SELECT
        a.*,
        a."no" AS indicator,
        a.num 
    FROM
        a 
    WHERE
        a.a = a.b 
    UNION
    ALL SELECT
        a.*,
        a."yes" AS indicator,
        a.num 
    FROM
        a 
    WHERE
        a.a = a.c 
    UNION
    ALL SELECT
        a.*,
        a."no" AS indicator,
        a."All" AS num 
    FROM
        a 
    WHERE
        a.a = a.b 
    UNION
    ALL SELECT
        a.*,
        a."yes" AS indicator,
        a."All" AS num 
    FROM
        a 
    WHERE
        a.a = a.c