The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 42): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `participant1` is indexed, the index won’t be used as it’s wrapped with the function `IF`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 42): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `participant2` is indexed, the index won’t be used as it’s wrapped with the function `IF`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 87): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `participant1` is indexed, the index won’t be used as it’s wrapped with the function `IF`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Calling Functions With Indexed Columns (query line: 87): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `participant2` is indexed, the index won’t be used as it’s wrapped with the function `IF`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
5. Avoid OFFSET In LIMIT Clause (query line: 102): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
6. Avoid OFFSET In LIMIT Clause (query line: 53): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
7. Avoid OFFSET In LIMIT Clause (query line: 96): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
8. Avoid OFFSET In LIMIT Clause (query line: 32): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
9. Avoid OFFSET In LIMIT Clause (query line: 77): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
10. Avoid OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
11. 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.
12. Mixed Order By Directions Prevents Index Use (query line: 100): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
13. Mixed Order By Directions Prevents Index Use (query line: 51): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
14. Mixed Order By Directions Prevents Index Use (query line: 94): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
15. Use UNION ALL instead of UNION (query line: 56): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.

Optimal indexes for this query:

ALTER TABLE `wit_game_turns` ADD INDEX `wit_turns_idx_game_id_id` (`game_id`,`id`);
ALTER TABLE `wit_game_turns` ADD INDEX `wit_turns_idx_id` (`id`);
ALTER TABLE `wit_games` ADD INDEX `wit_games_idx_status_participant2` (`status`,`participant2`);
ALTER TABLE `wit_games` ADD INDEX `wit_games_idx_status_participant1` (`status`,`participant1`);

The optimized query:

SELECT
        gameId,
        wit_games_turn_count,
        opponentId,
        name,
        wit_users_login_section,
        wit_users_image,
        wit_users_email,
        turnSts 
    FROM
        ((SELECT
            wit_games.id AS gameId,
            wit_games.turn_count AS wit_games_turn_count,
            wit_users.id AS opponentId,
            wit_users.username AS name,
            wit_users.login_section AS wit_users_login_section,
            wit_users.image AS wit_users_image,
            wit_users.email AS wit_users_email,
            Ifnull((SELECT
                IF(wit_game_turns.user_id = 911,
                IF(wit_game_turns.status = 1,
                2,
                3),
                IF(wit_game_turns.status = 1,
                4,
                1)) 
            FROM
                wit_game_turns 
            WHERE
                wit_game_turns.game_id = wit_games.id 
            ORDER BY
                wit_game_turns.id DESC LIMIT 0,
                1),
            IF(wit_games.participant1 = 911,
            1,
            3)) AS turnSts,
            wit_users.login_sts AS wit_users_login_sts 
        FROM
            wit_games 
        LEFT JOIN
            wit_users 
                ON wit_users.id = IF(wit_games.participant1 = 911,
            wit_games.participant2,
            wit_games.participant1) 
        WHERE
            wit_games.status = 1 
            AND (
                wit_games.participant2 = 911
            ) 
        ORDER BY
            turnsts DESC,
            wit_users_login_sts DESC,
            wit_games.id LIMIT 0,
            100) 
        UNION
        DISTINCT (SELECT
            wit_games.id AS gameId,
            wit_games.turn_count AS wit_games_turn_count,
            wit_users.id AS opponentId,
            wit_users.username AS name,
            wit_users.login_section AS wit_users_login_section,
            wit_users.image AS wit_users_image,
            wit_users.email AS wit_users_email,
            Ifnull((SELECT
                IF(wit_game_turns.user_id = 911,
                IF(wit_game_turns.status = 1,
                2,
                3),
                IF(wit_game_turns.status = 1,
                4,
                1)) 
            FROM
                wit_game_turns 
            WHERE
                wit_game_turns.game_id = wit_games.id 
            ORDER BY
                wit_game_turns.id DESC LIMIT 0,
                1),
            IF(wit_games.participant1 = 911,
            1,
            3)) AS turnSts,
            wit_users.login_sts AS wit_users_login_sts 
        FROM
            wit_games 
        LEFT JOIN
            wit_users 
                ON wit_users.id = IF(wit_games.participant1 = 911,
            wit_games.participant2,
            wit_games.participant1) 
        WHERE
            wit_games.status = 1 
            AND (wit_games.participant1 = 911) 
        ORDER BY
            turnsts DESC,
            wit_users_login_sts DESC,
            wit_games.id LIMIT 0,
            100)
    ) AS union1 
ORDER BY
    union1.turnsts DESC,
    union1.wit_users_login_sts DESC,
    union1.gameId LIMIT 0,
    100