The optimization process and recommendations:

1. Avoid OFFSET In LIMIT Clause (query line: 55): 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.
2. Avoid using SQL_CALC_FOUND_ROWS (query line: 2): Including SQL_CALC_FOUND_ROWS statements tend to slow down queries significantly as it doesn't scale well. It's recommended to split this query to two: a data selection query and a counting query.
3. 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.
4. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `team_football_1001_postmeta`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.

Optimal indexes for this query:

ALTER TABLE `team_football_1001_postmeta` ADD INDEX `team_1001_idx_meta_key_post_id` (`meta_key`,`post_id`);
ALTER TABLE `team_football_1001_posts` ADD INDEX `team_1001_idx_post_type_post_statu_id` (`post_type`,`post_status`,`ID`);

The optimized query:

SELECT
        SQL_CALC_FOUND_ROWS team_football_1001_posts.ID 
    FROM
        team_football_1001_posts 
    INNER JOIN
        team_football_1001_postmeta 
            ON (
                team_football_1001_posts.ID = team_football_1001_postmeta.post_id
            ) 
    LEFT JOIN
        team_football_1001_postmeta AS mt1 
            ON (
                team_football_1001_posts.ID = mt1.post_id
            ) 
    LEFT JOIN
        team_football_1001_postmeta AS mt2 
            ON (
                team_football_1001_posts.ID = mt2.post_id
            ) 
    LEFT JOIN
        team_football_1001_postmeta AS mt3 
            ON (
                team_football_1001_posts.ID = mt3.post_id 
                AND mt3.meta_key = '_team_winner_id'
            ) 
    WHERE
        1 = 1 
        AND (
            team_football_1001_postmeta.meta_key = '_date_start' 
            AND (
                (
                    (
                        mt1.meta_key = '_date_start' 
                        AND mt1.meta_value > '2017-07-07 06:48:49'
                    ) 
                    OR (
                        (
                            mt2.meta_key = '_date_start' 
                            AND mt2.meta_value < '2017-07-07 06:48:49'
                        ) 
                        AND mt3.post_id IS NULL
                    )
                )
            )
        ) 
        AND team_football_1001_posts.post_type = 'matches' 
        AND (
            (
                team_football_1001_posts.post_status = 'publish'
            )
        ) 
    GROUP BY
        team_football_1001_posts.ID 
    ORDER BY
        team_football_1001_postmeta.meta_value DESC LIMIT 0,
        3