The optimization process and recommendations:

1. Avoid OFFSET In LIMIT Clause (query line: 58): 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. 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.
3. Sort and Limit Before Joining (modified query below): In cases where the joins aren't filtering any rows, it's possible to sort and limit the amount of rows using a subquery in the FROM clause, before applying the joins to all other tables.

Optimal indexes for this query:

ALTER TABLE `Site` ADD INDEX `site_idx_id` (`id`);
ALTER TABLE `conversation` ADD INDEX `conversation_idx_listing_id` (`listing_id`);
ALTER TABLE `guest_data` ADD INDEX `guest_data_idx_id` (`id`);
ALTER TABLE `listing` ADD INDEX `listing_idx_site_id` (`site_id`);
ALTER TABLE `message` ADD INDEX `message_idx_created_at` (`created_at`);
ALTER TABLE `user_` ADD INDEX `user__idx_id` (`id`);

The optimized query:

SELECT
        id0 AS id0,
        content1 AS content1,
        created_at2 AS created_at2,
        c1_.id AS id3,
        l2_.id AS id4,
        l2_.reference AS reference5,
        s3_.id AS id6,
        s3_.name AS name7,
        s3_.code AS code8,
        u4_.email AS email9,
        u4_.id AS id10,
        u4_.firstname AS firstname11,
        u4_.lastname AS lastname12,
        u5_.email AS email13,
        u5_.id AS id14,
        u5_.firstname AS firstname15,
        u5_.lastname AS lastname16,
        g6_.id AS id17,
        g6_.firstname AS firstname18,
        g6_.lastname AS lastname19,
        g6_.email AS email20,
        conversation_id21 AS conversation_id21,
        author_user_id22 AS author_user_id22,
        author_guest_id23 AS author_guest_id23,
        c1_.author_user_id AS author_user_id24,
        c1_.author_guest_id AS author_guest_id25,
        c1_.listing_id AS listing_id26,
        l2_.poster_id AS poster_id27,
        l2_.site_id AS site_id28,
        l2_.building_id AS building_id29,
        l2_.type_id AS type_id30,
        l2_.neighborhood_id AS neighborhood_id31,
        l2_.facility_bathroom_id AS facility_bathroom_id32,
        l2_.facility_kitchen_id AS facility_kitchen_id33,
        l2_.facility_heating_id AS facility_heating_id34,
        l2_.facility_internet_id AS facility_internet_id35,
        l2_.facility_condition_id AS facility_condition_id36,
        l2_.original_translation_id AS original_translation_id37,
        u4_.site_id AS site_id38,
        u4_.address_id AS address_id39,
        u4_.billing_address_id AS billing_address_id40,
        u5_.site_id AS site_id41,
        u5_.address_id AS address_id42,
        u5_.billing_address_id AS billing_address_id43,
        g6_.site_id AS site_id44 
    FROM
        (SELECT
            m0_.id AS id0,
            m0_.content AS content1,
            m0_.created_at AS created_at2,
            m0_.conversation_id AS conversation_id21,
            m0_.author_user_id AS author_user_id22,
            m0_.author_guest_id AS author_guest_id23 
        FROM
            message m0_ 
        ORDER BY
            m0_.created_at DESC LIMIT 25 OFFSET 0) m0_ 
    INNER JOIN
        conversation c1_ 
            ON m0_.conversation_id21 = c1_.id 
    INNER JOIN
        listing l2_ 
            ON c1_.listing_id = l2_.id 
    INNER JOIN
        Site s3_ 
            ON l2_.site_id = s3_.id 
    INNER JOIN
        user_ u4_ 
            ON l2_.poster_id = u4_.id 
    LEFT JOIN
        user_ u5_ 
            ON m0_.author_user_id22 = u5_.id 
    LEFT JOIN
        guest_data g6_ 
            ON m0_.author_guest_id23 = g6_.id 
    WHERE
        s3_.id = 287 LIMIT 25